Using date dropdown to manipulate a card that aggregates using two different dates

Jeffsnake

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?

jhl

Hi,

 

that is an interesting question.

 

As far as I understand, you essentially want to perform a calculation that would boil down to the definition of a window function (which beast mode does not support): performing calculations over different columns AND multiple rows at the same time. Beast mode does not allow for that currently, you can first run a column operation (like SUM()) or a row operation (like `Value1`+`VALUE2`) followed by the other, but you need this to be dynamic. 

(May the majors correct me if I am wrong on this)

 

If the structure of your data does not allow for the analysis you are trying to do, you will need to transform your data. There may be some hack that I am not aware of, but beast mode does not transform the data itself, but does calculations on your data.

 

My suggestion would be the following (especially if you are not an SQL expert, creating dynamic datasets is tricky 

1) use Magic ETL

2) add in a date dataset with just one column

(I think Domo has one available you can request from your customer success manager, we have a dataset of historical exchange rates going back a few years with some projected rates from a Google Sheet filled with Google finance data that we use for grouping data for other purposes, making an excel file and just pulling down a date for 20 seconds could also do the trick but isnt automatic- you should have one of these if you do not yet, it is very useful)

3) "select columns" on your `order taken` column

4) add a column with a constant of 1 and "group by" day --> this is the "orders taken that day" on that date

5) repeat 3) and 4) for the `order fulfilled` field --> "orders fulfilled that day"

6) join the two pairs of columns on the date dataset (you can also drop the order taken and order fulfilled date columns to make it more readable)

 

 

Now you should end up with something like 

"Date"  "orders taken that day" "orders fulfilled that day"

1-1-2000 123 456

1-2-2000 234 567

... 

and so on.

 

Now you can aggregate in ETL by month, quarter, whatever you want, or just feed the transformed data into your card and aggregate in there.

 

This should work, let me know if you find another solution.

 

 

HTH

Jeffsnake

jhl - I like this idea... I'm pretty sure you are correct about not being able to do this natively in a card using only filters and beastmode.

 

You basically took my idea about using a workflow to roll up by month before hand and improved upon it.  Rolling up by day and then using a left join from a "seed" dataset that contains all dates should work great.

 

I'll give it a try in the next couple days and post any learnings.

 

Thanks,

Jeff