Forum Discussion

shishir999's avatar
shishir999
Helper II
7 years ago
Solved

Calculate Difference between change in value

Hi,   In general, we calculate the Cummulative Total of the values, but my requirement is to get the each day change in value based on Cummulative values. See the attachement.      Left si...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    shishir999

     

    Try this pattern

     

    Calc col =
[Value]
    - MINX (
        TOPN (
            1,
            FILTER ( Table1, [Ctg] = EARLIER ( [Ctg] ) && [Date] < EARLIER ( [Date] ) ),
            [Date], DESC
        ),
        [Value]
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
7 years ago

shishir999

 

This is how it works for each row to get the previous Cumulative Value

 

1) First Filter gets a filtered table with all dates less than current row date

2) TOPN on this Filtered Table gets the lastrow from the Filtered Table

3) MINX is used to get the Value on this Date

 

so basically the flow is MINX<<<TOPN<<<<Filter

shishir999's avatar
shishir999
Helper II
7 years ago

Hi Zubair,

 

I need one more help here.

 

 

In the same query, how can I restrict that my 1st min. value must remain ZERO and after that, if any changes in the value, it will calculate it, as the exisiting query working.

 

 

 

Thanks

Shishir G.