Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
StacyCui
Creator
Creator

Accumulative Value is 0

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)

StacyCui_0-1670036129270.png

StacyCui_1-1670036146450.png

1 Solution

Accepted Solutions
rubenmarin

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.

View solution in original post

6 Replies
rubenmarin

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
Creator
Creator
Author

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?

StacyCui_1-1670060763903.png

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
Creator
Creator
Author

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 .

StacyCui_0-1670069276008.png

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
Creator
Creator
Author

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