Aggregate by most recent not-null value

0
votes

I have a dataset with the following columns [ product_id, country_id, date, number_of_installs, cumulative_installs_last_30_days ]

I have no problem applying the standard measures to find the sum, max or average number_of_installs within those three dimensions (product_id, country_id, date(aggregated by month or week)). However, I have not been able to aggregate by cumulative_installs_last_30_days because as that variable is already a cumulative, I need to return the “most recent value” and Tableau does not have that option built-in the aggregation functions.

How do I create a Calculated Field that enables an addicional column in the aggregated dataset with the most recent not-null value of cumulativeInstalls_last_30_days within the dimensions product_id, country_id and date(aggregated by month or week)?

1
tableau-api
Do [number_of_installs] and [cumulative_installs_last_30_days] match up in the data? For example, if you were to sum [number_of_installs] from January 1 to January 30, would you get the number in [cumulative_installs_last_30_days] for January 30? If so, it will probably be easier to create a calculated field to perform that aggregation for you. – Andrew LaPrise
@alaprise, they do match up. The "30_days" were an example as the number may change. Also I may need to aggregate by month and the "cumulative_installs_..." may be longer or shorter than 30 days. Also, the variable needs to be relative to "today". I have looked for a solution in the Tableau forums and have not found a solution yet. – user3307599

1 Answers

2
votes

Here's a dirty solution.

In the comments, you noted that you wanted that 30 days to be dynamic, so to accomplish that, create a parameter, make it an integer, select Range, and allow any integer over zero. I'll call it [Number of Days].

Then create a calculated field:

TOTAL(SUM(IIF(DATEDIFF("day", [date], TODAY()) < [Number of Days], [Number of Installs], NULL)))

I know that's ridonk, so I'll break it down, from the inside out.

DATEDIFF("day", [date], TODAY())

That just calculates the difference in days between today and the date in a given row.

IIF(DATEDIFF("day", [date], TODAY()) < [Number of Days], [Number of Installs], NULL)

That checks if that difference is less than the number of days you selected. If it is, this statement is equal to the number of installs. If it's not, it's null. As a result, if we sum all of these values, we only get the number of installs in the last [Number of Days] days.

With that in mind, we SUM() the rows. TOTAL() just performs that sum over every database row that contributes to the partition.

Note that if your database has dates after TODAY(), you'll need to add another condition to that IIF() statement to make sure those aren't included.

You also mentioned that you want to be able to aggregate the number of installs by month. That's MUCH easier. Just toss MONTH([date]) into the dashboard, then SUM([Number of Installs]), and Tableau will knock it out for you.