topic Set analysis using logical dimension? in QlikView

Set analysis using logical dimension?

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

Hi All,@

Please refer to the attached app, how to use below logical dimension expression(item 1) in my set analysis expression ( item 2)

item1:

Logical Dimension as below "Repeat"

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

item2:

in Expression

=Aggr(count({<soh={">1"},vRepeat={"Repeat"}>} distinct Batch),MaterialHuddleGrpDesc,ShopNo)

tried below way and not giving correct results. tks

Created Variable as "vRepeat"

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

FYI,

please let me know if the requirement not clear. thanks, Deva

Re: Set analysis using logical dimension?

devarasu07 — Tue, 27 Dec 2016 13:42:19 GMT

stalwar1‌ your thoughts on this, please

Re: Set analysis using logical dimension?

sunny_talwar — Tue, 27 Dec 2016 13:50:28 GMT

You cannot use an expression on the left hand side of a set modifier. Your vRepeat won't work. Having said that, I don't really understand the logic you are trying to implement here, but can you see if this give the right result (The result doesn't completely match, but may be you have provided a slightly incorrect output?)

=Count({<soh={">1"},ArticleNo = {"=Count({<Year={'*'},Week={'*'}, Type_ID={'RO'}>} DISTINCT OrderQty) > 0"}>} DISTINCT Batch)

Re: Set analysis using logical dimension?

devarasu07 — Tue, 27 Dec 2016 22:38:39 GMT

Hi Sunny,

Thanks for the solution, i've small issue on the formatting column header in pivot table. do you have any idea on this? thanks

i've tried with Grid mode option and background color option in chart dimension & expression but column header background issue.

FYI, current output with background

expected background color:

Re: Set analysis using logical dimension?

sunny_talwar — Tue, 27 Dec 2016 23:23:32 GMT

So you want Var% to be white??? I think you can convert your expressions into a dimension and just use one expression to get what you are looking for. That is the only approach I can think of right now.