topic QlikView Set Analysis in QlikView

QlikView Set Analysis

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

Hello, Below is the set analysis expression we have in a QlikView chart. It had worked previously and now it is broken. I do not know exactly when it broke (ex: after an upgrade,etc.). The second expression does work after removing a field from the TOTAL.

Broken expression:

=if(

sum(PUVisit_Count) > 0

and

sum({<

[CPT Category]=

,[CPT Sub Category]=

,[CPT Group]=

,[CPT w Desc Ordered]=

//,Ordering=

TOTAL <Site,[Site Mnem]> PUVisit_Count) >= vPUMinVisitCount

sum({<

[CPT Category]=

,[CPT Sub Category]=

,[CPT Group]=

,[CPT w Desc Ordered]=

//,Ordering=

TOTAL <Site,[Site Mnem]> PUVisit_Count)

Null()

)

Removing "Site" from the total makes the expression work:

=if(

sum(PUVisit_Count) > 0

and

sum({<

[CPT Category]=

,[CPT Sub Category]=

,[CPT Group]=

,[CPT w Desc Ordered]=

//,Ordering=

TOTAL </*Site,*/[Site Mnem]> PUVisit_Count) >= vPUMinVisitCount

sum({<

[CPT Category]=

,[CPT Sub Category]=

,[CPT Group]=

,[CPT w Desc Ordered]=

//,Ordering=

TOTAL </*Site,*/[Site Mnem]> PUVisit_Count)

Null()

)

Any ideas as to why it was working, and then it stopped working? I tried adding in square brackets, copying and pasting the field name and some other things with no luck. Also, please do not ask me to share the qvw.

Any help is appreciated. Thanks!

Re: QlikView Set Analysis

vishsaggi — Tue, 14 Mar 2017 15:35:27 GMT

Is your field Site in your data model and has any data in there? can you share a sample app if possible ?

Re: QlikView Set Analysis

hlines_usacs — Tue, 14 Mar 2017 16:09:28 GMT

Yes, Site is a field and it does have data.

Re: QlikView Set Analysis

vishsaggi — Tue, 14 Mar 2017 16:11:13 GMT

Can you share your app and tell us what is your expected output with some sample? It will be easy to work on it quickly.

Re: QlikView Set Analysis

Anil_Babu_Samineni — Tue, 14 Mar 2017 16:14:44 GMT

TOTAL </*Site,*/[Site Mnem], PUVisit_Count)> >= vPUMinVisitCount

Can you delete extra angular brackets. and add more Comma

Re: QlikView Set Analysis

hlines_usacs — Tue, 14 Mar 2017 16:19:56 GMT

Did you happen to copy the wrong thing? My original post doesn't have any extra angle brackets.

Re: QlikView Set Analysis

sunny_talwar — Tue, 14 Mar 2017 16:36:11 GMT

What is your chart dimensions here?

Re: QlikView Set Analysis

hlines_usacs — Tue, 14 Mar 2017 16:37:27 GMT

=if(

aggr(sum( PUVisit_Count ), Site) > 0

Left(Site, 10) & '..(' & [Site Mnem] & ')'

Null()

)

Re: QlikView Set Analysis

sunny_talwar — Tue, 14 Mar 2017 16:39:56 GMT

Do you even need TOTAL here? seems like you just have 2 fields in the dimension... can you not just use this

=if(

sum(PUVisit_Count) > 0

and

sum({<

[CPT Category]=

,[CPT Sub Category]=

,[CPT Group]=

,[CPT w Desc Ordered]=

//,Ordering=

>} PUVisit_Count) >= vPUMinVisitCount

sum({<

[CPT Category]=

,[CPT Sub Category]=

,[CPT Group]=

,[CPT w Desc Ordered]=

//,Ordering=

>} PUVisit_Count)

Null()

)

Re: QlikView Set Analysis

hlines_usacs — Tue, 14 Mar 2017 16:42:53 GMT

It needs to be TOTAL because this expression is named "Total Visits" and then Total Visits is used in other expressions. So this specific expression isn't a line or bar in the chart, but it is used as denominator in the other expressions that are shown in the chart.

Re: QlikView Set Analysis

sunny_talwar — Tue, 14 Mar 2017 16:49:57 GMT

But you are doing a TOTAL on both your dimension fields... which should be equivalent of having no TOTAL at all (unless I am missing something here).... Would you be able to share an instance when it was actually working? I am curious to see how it worked before so that I can help you with how it can work now