Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
vidhyamurali
New Contributor

Expression does not work correctly for a few rows

Capture1.PNG

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.

 

Labels (3)
3 Replies

Re: Expression does not work correctly for a few rows

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,
Great dreamer's dreams never fulfilled, they are always transcended.

Re: Expression does not work correctly for a few rows

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Expression does not work correctly for a few rows

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])))