Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
musketeers
Creator
Creator

iSSUE WITH SET ANALYSIS

Hi, I need a help in comples set analysis.

For the below data, we have multiple Products and Error ID. Error IDs are related to few Products. The requirement is to get the Sum of Volume for Error IDs but the twist is that for the given date we need to find the related Products and for those products we need to find the sum of Volume. So the output of the below data should be:

DATE ERROR_ID TOTAL_VOLUME
1/10/2023 ERR_1 608
2/10/2023 ERR_2 667

 

I have written the below set analysis but its not working:

SUM({<Product=p({<Error_ID=p(Error_ID)>}Product)>}Volume)

Could you please help me to acheive this output.

Thanks in advance.

The load statement of the sample data is as below:

load * Inline[
Date, Product, Error_ID, Volume
1/10/2023, P1 ,, 23
1/10/2023, P1 ,,24
1/10/2023, P1, , 25
1/10/2023, P1, ERR_1, 26
1/10/2023, P2 ,, 27
1/10/2023, P2,, 28
1/10/2023, P2 ,ERR_1, 29
1/10/2023, P3 ,, 30
1/10/2023, P3 ,, 31
1/10/2023, P3 ,, 32
1/10/2023, P3 ,, 33
1/10/2023, P3 ,, 34
1/10/2023, P3 ,ERR_1, 35
1/10/2023, P4 ,, 36
1/10/2023, P4 ,, 37
1/10/2023, P4 ,, 38
1/10/2023, P4 ,ERR_1, 39
1/10/2023, P4 ,, 40
1/10/2023, P4 ,, 41
1/10/2023, P5 ,, 23
2/10/2023, P5 ,, 24
2/10/2023, P5 ,, 25
2/10/2023, P5 ,ERR_2, 26
2/10/2023, P6 ,, 27
2/10/2023, P6 ,, 28
2/10/2023, P6 ,ERR_2, 29
2/10/2023, P7 ,, 30
2/10/2023, P7 ,, 31
2/10/2023, P7 ,, 32
2/10/2023, P7 ,, 33
2/10/2023, P7 ,, 34
2/10/2023, P7 ,ERR_2, 35
2/10/2023, P8 ,, 36
2/10/2023, P8 ,, 37
2/10/2023, P8 ,, 38
2/10/2023, P8 ,ERR_2, 39
2/10/2023, P8 ,, 40
2/10/2023, P8 ,, 100

];

 

Labels (2)
12 Replies
vincent_ardiet_
Specialist
Specialist

Try:
SUM({<Error_ID={*}>}Volume)

musketeers
Creator
Creator
Author

Hi Vincent,

Thanks for your time. This set analysis only gives the Volume sum for rows where Error ID is given. Not the required output.

Ahidhar
Creator III
Creator III

you want the sum of volume of only those Prodcuts which have an ERROR_ID ?

musketeers
Creator
Creator
Author

Hi, No. I need sum of all the Products related to a specific error id for selected date.

For example for Oct 1 and for Error code ERR_1 we have Products P1,P2,P3 and P4. So we need to display SUM of all Volumes for these products for Oct 1.

vincent_ardiet_
Specialist
Specialist

Not sure this is possible without modifying the datamodel.
Don't forget that a set analysis is working like doing selections in a filter. So, if you when you want all products with an error code, this is not ventilated by date. And P5 for example, as it is associated to an error code for the 2nd of October, is also counted for the 1st of October if you use p({<Error_ID={"*"}>}Product).

Gabbar
Specialist
Specialist

Why not create a new column in Load Script to give you error ID in a different column where you Require and Use That in Charts.

MainTable:
Load * from Source;
Left join(MainTable)
Load distinct Product,Error_ID as New_Error resident MainTable where not Isnull(Error_ID);
and then just use the column New_Error in Filter Pane.
You can also add multiple Columns in Second table for your required Type.

That Should Work For every type, But if you specifically want to do this in Dashboard side, you will need a calculative Dimension. 
This is the output after that calculated Dimension. 

Gabbar_0-1701685126747.png

Dimension Expression :- =Coalesce(Error_ID,aggr(Only({< >}Error_ID),Product))

Please adjust the only Function to your requirements. 

Ahidhar
Creator III
Creator III

I am getting different values 

Gabbar
Specialist
Specialist

Could you please tell show me this output in your system:-

Gabbar_0-1701686938987.png

Second Dimension expression :- aggr(Only({<Error_ID={"*"} >}Error_ID),Product),
Also another With :- aggr(Only(Error_ID),Product)

Ahidhar
Creator III
Creator III

getting null using both