iSSUE WITH SET ANALYSIS

[musketeers]

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

];

[vincent_ardiet_]

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

[musketeers]

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]

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

[musketeers]

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_]

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]

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. 

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

Please adjust the only Function to your requirements. 

[Ahidhar]

I am getting different values 

[Gabbar]

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

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

[Ahidhar]

getting null using both