cancel
Showing results for 
Search instead for 
Did you mean: 

Preserving YTD after date filter applied

jirazazabal
9 - Travel Pro
9 - Travel Pro

Hi,

We are facing this. We have a pivot table with shows the total amount of a metric and the running sum form the begining of the year (YTD). This is working fine:

jirazazabal_0-1675083680442.png

Then, we want to apply a filter based on the date column, to show just a couple of months. If we do taht, we are loosing the YTDSUM calculation, and it applies only in this couple of months

jirazazabal_1-1675083814038.png

We are expecting 16M and 17M in the third column, but we are getting just the running sum of the couple of months. We have tried with ALL but the results are not the expected. 

Thanks

3 REPLIES 3

cole_marquard
8 - Cloud Apps
8 - Cloud Apps

Faced with a similar problem, my solution was to add another column into the table of the elasticube/live which was the cumulative YTD sum and display as a table in place of a pivot. Unfortunate to have to work around, but others might have more convenient solutions. 

I have thought about this solution, but I cannot indentify the YTD function in Elasticube, how did you achieve that?

cole_marquard
8 - Cloud Apps
8 - Cloud Apps

I generally use an sql window function when importing the table, ie "over partition by..". Given what is shown is a pivot I assume your data does not already look like that so I could not give you the exact query just the method