Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
56
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
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.
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
........
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.
Best I have are the following links:
Help:
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