topic Re: weird behavior with pivot table or set with agg expression? in QlikView

weird behavior with pivot table or set with agg expression?

devarasu07 — Wed, 25 Nov 2020 16:16:04 GMT

Please refer to the sample attachment, I've pivot table report with calculated dimension and expression used. facing below issue. is this expression issue or pivot table bug? kindly advise me. Thanks

Issue:

I have list box which contains two values (SLG and Shades). if i select Shades the pivot table dimensional column missing. seems agg with set analysis is makking issue or something else?

Note: No issue when i select other filter value (SLG), only pivot table column dispapper when we selected as "Shades"

Thanks, Deva

Re: weird behavior with pivot table or set with agg expression?

sunny_talwar — Tue, 24 Jan 2017 17:29:35 GMT

I am seeing this issue and someone else also pointed it out here:

Dimension(Column) missing in Pivot when there is Null

Re: weird behavior with pivot table or set with agg expression?

devarasu07 — Tue, 24 Jan 2017 17:41:18 GMT

Hi Sunny,

Thanks for your help, but my case Primary key field missing (it's not null value). but other calculated dimension has null will this cause the problem? thanks

Regards, Dev

Re: weird behavior with pivot table or set with agg expression?

sunny_talwar — Tue, 24 Jan 2017 17:49:23 GMT

I think calculated dimensions are what is causing issue here.... Not sure I understand the cause....

Re: weird behavior with pivot table or set with agg expression?

rwunderlich — Tue, 24 Jan 2017 18:30:34 GMT

I think your problem lies in the 3rd Calculated Dimension labeled "Theme". The if() returns null because SKUTheme contains no values for Shades. Yo can fix it by wrapping the if() in an aggr() to preserve the dimensionality.

=aggr(

..long nested if

,Launch_No,SKUTheme)

The dimension labeled "Repeat" is also problematic when selecting SLG. Not sure what the fix is for that one.

-Rob

Re: weird behavior with pivot table or set with agg expression?

devarasu07 — Tue, 24 Jan 2017 18:52:31 GMT

Hi Rob,

Thanks for your valuable input, tried with your suggestion but still it's not working. is there any alternate method to write below expression? kindly advise. once again thank you

=aggr(if((Count({<Year={'*'},Week={'*'},Type_ID={'RO'},Delivery_status={'Ongoing'}>} distinct OrderQty))>0, 'Repeat', '0'),Launch_No,SKUTheme, ArticleNo)

Best Regards,Deva

Re: weird behavior with pivot table or set with agg expression?

rwunderlich — Wed, 25 Jan 2017 05:01:34 GMT

Add an inner aggr() to your original expression:

aggr(if(aggr(Count({<Year={'*'},Week={'*'},Type_ID={'RO'}>} distinct OrderQty),ArticleNo)>0, 'Repeat', '0'),ArticleNo)

And this only makes sense to me if you move this dimension after ArticleNo in the chart.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: weird behavior with pivot table or set with agg expression?

devarasu07 — Wed, 25 Jan 2017 06:18:38 GMT

Hi Rob,

It's working fine now, Thanks

Regards,

Deva