Accumulative Value is 0

[StacyCui]

Hi, All I have an order table in CY and PY. I'd like to calculate accumulative value. For instance: If now is P3 and I choose P5, could show the accumulative value. Here is my function. So far, It's OK. But if choose other selection like Category, The accumulative value will show 0, What does cause it?

Sum({<FY={'CY'},FROZENDATE= {"<=$(=max(FROZENDATE))"},Month>} ORVALUE)

[rubenmarin · Accepted solution]

Hi, you can try adding a table with all the used fields: Category, FY, FROZENDATE and ORVALUE as dimensions, and another table or text to show =max(FROZENDATE).

There you can see what changes when you select the category, I suppose is something with the =max(FROZENDATE), on set analysis this is calculated globally once, so all categories apply the same filter, and with the selection of a category this expression may change it's value.

[StacyCui]

Hi, Thank you for your reply. I tried to create two tables as you said. When I selected VM automotive in category. The max(FrozenDate) is 12/02/2022. That's yesterday. So, If I choose the future date. it also apply to the category and show 0. Is it right? Is there any solution for this? or I re-create a master calendar table for period?

[rubenmarin]

Hi, I don't see the FY field in there, just to check that the value it's still there when you select a category.

From what I see in that image Sum({<FY={'CY'},FROZENDATE= {"<=$(=max(FROZENDATE))"},Month>} ORVALUE) should work, try also adding the Month field to the table

[StacyCui]

Maybe I got the reason. Due to my calendar P1-P12 is a single month. So If I choose the P6, only show the PY .

[rubenmarin]

Hi, that's a hint, still it doesn't explains why it stops working when you select a category, maybe categories are only related to months in CY, and when a category is selected it restricts data to those months.

[StacyCui]

Hi, I have solve this issues, I changed master calendar table into period. For instance, p1 is from 2022/10/1 to 2022/10/30. P2 is from 2022/10/1 to 2022/11/30 so on and so forth. Your reply remind me and I learnt more from this issue. Thank you so much