weird behavior with pivot table or set with agg expression?

[devarasu07]

Hi

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

[sunny_talwar]

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

Dimension(Column) missing in Pivot when there is Null

[devarasu07]

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

[sunny_talwar]

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

[rwunderlich]

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

[devarasu07]

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

[rwunderlich · Accepted solution]

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

[devarasu07]

Hi Rob,

It's working fine now, Thanks

Regards,

Deva