Forum Discussion

jirazazabal's avatar
jirazazabal
Cloud Apps
01-30-2023

Preserving YTD after date filter applied

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:

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

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

dashboards & reporting

3 Replies

Replies have been turned off for this discussion
  • cole_marquard's avatar
    cole_marquard
    Cloud Apps
    01-30-2023

    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. 

    • jirazazabal's avatar
      jirazazabal
      Cloud Apps
      01-31-2023

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

  • cole_marquard's avatar
    cole_marquard
    Cloud Apps
    01-31-2023

    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