Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hey I am using this function in my table:
SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)
+
SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)
and I cant get the result as expected, it only show the value of this expression (the last one): SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)
what am I doing wrong?
What if you create a straight table With the dimession(s) you want and makes two expression
SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=date(YearStart(MAX(DATEPERIOD))-31))<=$(=date(YearStart(MAX(DATEPERIOD))-1))'},Year,Month,Quarter>}amount)
and then
SUM({$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)
will it shows correct on row Level?
I suggest to check each part separately within a table-chart without an expression-label - then within the expression-label qlik will show you how the expression and especially the $-sign expansion will be interpreted - and quite probably you will see which part you need to adjust. Quite probably this will be the formatting-issue.
- Marcus
It works perfectly in straight table
Ok,
what if you add a 3:rd Expression column(1)+column(2)?
it works in straight table, but not in pivot table
I am using pivot table
Ok, Can you send the application if possible
Then aggr() could help, can you show us what Dimension you have in Pivot.
sorry for the late reply.
it actually works fine if I dont select month as dimension. but if I select month as my dimension, it wont work
I have the dame problem and I am aware where it comes from but could not find a solution. The problem comes from the fact that the "+" between the two expressions is not effective and only one of them will be calculated and shows as a result. So theoretically we need a "sum" function to sum the two expressions instead of the +, which I could not get right till now.
I will write you the answer when I find it 🙂
So what I found is like using Total after the sum will get you some good results (only when you use one dimension), however when you use two dimensions for the X-axis you will get the same exact results every time (Let say you have Quaters and Years as your dimensions, by then you will get same values of the Quarters repeated over the years because Total ignores the dimension).
I am not sure how it will turn out in your expression but try this:
SUM(total{$<[Is Budget]={'0'},DATEPERIOD={'>=$(=YEARSTART(MAX(DATEPERIOD)))<=$(=MAX(DATEPERIOD))'},Year,Month,Quarter>}amount)