Using date dropdown to manipulate a card that aggregates using two different dates
case study: I want to create a card that for a given date range, it returns the number of orders taken and the number of orders completed. The dataset has one record per order, and there is a "order taken" date field and a "order completed" field.
I could use SQL to create a dataflow that that creates one record per month and goes ahead and aggregates the orders taken and orders complete, and then use that for the card... and the user could just choose the months they want to include... but that is a little rigid.
If I want to allow the user to enter whatever date range they want in the card, and have it return count of "order taken" date and count "order completed" date... there doesn't seem to be a good way to do that. In the card, I would have to tie the date dropdown to one of those dates... but it wouldn't relect accurately for the second date.
In Psuedocode, I'd like to do something like the following:
- for the date dropdown, let it be a "variable(s)" not tied to a specific date in the dataset.
- The card would use an "or" statement to return any records where order taken or order completed date is in the date range
- Use beastmode formulas to aggregate the "orders taken" by using a Case/Sum... and including orders where the "order taken" date is between the date variables... and do the same for "orders completed" by using the order completed date in another beastmode formula.
Any suggestions for how to pull this off?