topic Re: Exclude the conditional dimension field from set analysis expression ? in QlikView

Exclude the conditional dimension field from set analysis expression ?

bob654321 — Wed, 10 Jul 2019 20:29:43 GMT

How can I exclude the conditional dimension completely from the expression using set analysis ?

Logic for the product column in the table is If(Match(Product,'A','B','C'),Product)

I'm using this expression below but the issue is product in the pivot table is a conditional dimension. so it is causing nulls because of if match statement . The nulls shouldn't be displayed at all. If I use suppress nulls option, the total sales doesn't display correctly.

Sum({<PRODUCT=>>} TOTAL <YEAR,MONTH,QUARTER,CLIENT,REGION> [SALES])

My pivot table shows as below

Feb 2019

Product Totals

A 56

B 56

C 56

D 56

Re: Exclude the conditional dimension field from set analysis expression ?

bharathadde — Wed, 10 Jul 2019 20:35:20 GMT

Please remove >(marked as Red below) this from Expression

Sum({<PRODUCT=>>} TOTAL <YEAR,MONTH,QUARTER,CLIENT,PRODUCT,REGION> [SALES])

Now Suppress Null Values for Dimension

If(Match(Product,'A','B','C'),Product)

Please find below screenshot to see the result I got

Re: Exclude the conditional dimension field from set analysis expression ?

bob654321 — Wed, 10 Jul 2019 20:42:10 GMT

Sorry it should be one > only. somehow I typed as >>.

I've tried your solution but the issue is when I suppress the nulls the total becomes 52 instead of 56.

Re: Exclude the conditional dimension field from set analysis expression ?

bharathadde — Wed, 10 Jul 2019 20:48:16 GMT

Can you send sample data with all the fields you have mentioned

like below

Product,YEAR,MONTH,QUARTER,CLIENT,REGION,Sales

A,2018,6,Q2,XYZ,R1,24

........

Re: Exclude the conditional dimension field from set analysis expression ?

bob654321 — Wed, 10 Jul 2019 22:29:08 GMT

I've attached the sample excel file . In the dataset the product is not dependent on the sales. Sales is only dependent on Region,Client,Year,Month and Quarter.

Re: Exclude the conditional dimension field from set analysis expression ?

Brett_Bleess — Thu, 01 Aug 2019 13:25:35 GMT

Best I have are the following links:

Help:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

Design Blog:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

Note on the Design Blog post there are other links at the bottom as well that may be pertinent here. Sorry I am not more help, but this may give you what you need here. If you have resolved things, please post your solution and mark it as the solution for others.

Regards,
Brett