topic Re: Expression does not work correctly for a few rows in QlikView

Expression does not work correctly for a few rows

vidhyamurali — Sat, 16 Nov 2024 04:53:51 GMT

Hi All,

I have this expression in a pivot. It gives the desired result for all rows, but for one row. I find this weird and I'm not sure how to debug this one. I appreciate any help with regard to this.

Logic:

row 1 = actual = column with values from table

row 2 = ** to test** = column with expression

the user wants the total of BG-C/MKT-C actuals number to be displayed for both BG-C and MKT-C when the dimension BU is selected.

eg: please see rows 1, 2 and 3

total of BG-C & MKT-C = -0 + 27 = 27

this total is displayed along both BG-C and MKT-C rows and also the sub-total row

Expression:

(sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}aggr(sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}[LY Actual MAT - EUR]),[BU],[BG],[Business Model],[Business]))/vUnit)

(this cannot be done in script because we use dynamic dimensions and there are many more dimensions that can be dynamically added apart from BU (shown here) and for each dimension this logic is different and hence the actuals (row 1) value has to be preserved for other logic))

Issue:

for almost all rows the expression works except for a few rows highlighted. I'm not sure how to debug this one. any help is appreciated.

Re: Expression does not work correctly for a few rows

PrashantSangle — Wed, 26 Dec 2018 09:39:49 GMT

Hi,

Try with below

Sum(aggr(sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}[LY Actual MAT - EUR]),[BU],[BG],[Business Model],[Business]))

Regards,

Re: Expression does not work correctly for a few rows

Anil_Babu_Samineni — Wed, 26 Dec 2018 10:25:51 GMT

For me your expression is correct.

(sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}aggr(sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}[LY Actual MAT - EUR]),[BU],[BG],[Business Model],[Business]))/vUnit)

But, Need to understand why outer set expression required? May be use NODISTINCT

(sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}aggr(NODISTINCT sum({<[BG-C/MKT-C]={'BG-C','MKT-C','World'}>}[LY Actual MAT - EUR]),[BU],[BG],[Business Model],[Business]))/vUnit)

Re: Expression does not work correctly for a few rows

sunny_talwar — Wed, 26 Dec 2018 12:25:09 GMT

I am curious to know what is

vUnit

Is this 0 for some reason on the rows highlighted? What do you see when you use your expression without vUnit

(Sum({<[BG-C/MKT-C] = {'BG-C','MKT-C','World'}>} Aggr(Sum({<[BG-C/MKT-C] = {'BG-C','MKT-C','World'}>} [LY Actual MAT - EUR]), [BU], [BG], [Business Model], [Business])))