Hello,
I have an issue using a variable in of the measure in the Table object. The calculation behaves kind of strange to me.
Here are the variable definitions:
vCusAvgRevLThreeMonths = sum({$<$(vIgnoreDateFieldsSelection),Revenues_flag={'YES'}, MonthsAgo={2,3,4}>}$(vAmount))/3
vLastMonthRevenue = Sum({<$(vIgnoreDateFieldsSelection),MonthsAgo={1},Revenues_flag={'YES'}>}$(vAmount))
*vIgnoreDateFieldsSelection includes just the date fields names seperated by commas - in order to ignore selections in these fields
Here are the measure definitions:
Cus. Avg Revenue - 3 Months before Last Month =
num($(vCusAvgRevLThreeMonths), $(vCurrencyPattern))
Last Month Revenue =
num($(vLastMonthRevenue),$(vCurrencyPattern))
Customer Trend Percentage =
if($(vLastMonthRevenue) < 0 or $(vCusAvgRevLThreeMonths) < 0,
null(),
$(vLastMonthRevenue)/$(vCusAvgRevLThreeMonths)-1)
Table gives me these results which are not matching to each other - Customer Trend Percentage is incorrect (correct result should be 0.11%
Customer Name | Cus. Avg Revenue - 3 Months before Last Month | Last Month Revenue | Customer Trend Percentage
Customer A | 14 320 | 14 337 | -88.88%
RESULT OK RESULT OK INCORRECT RESULT
It is strange though, that solution seems to be to wrap vCusAvgRevLThreeMonths variable in aggr() function with Customer Name used in it. But why does this work? It would make sense to me that, then I would have to do the same thing with vLastMonthRevenue variable too, but that doesn't seem to be necessary. How exactly Qlik Sense calculates this?
Any ideas are more than welcome. Maybe, I am missing something very trivial.
Thanks a lot. Have a great day.
