Dimension ignored by variable

onmysi49 — Sat, 16 Nov 2024 03:03:47 GMT

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.

topic Dimension ignored by variable in App Development

Dimension ignored by variable