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

];

[Gabbar]

I hope You have Loaded the sample Data that you gave us,
Now if you directly load that sample data then There are not nulls in ErrorID but Empty Strings.
Loaded This:-

and got this in Data

.
But I thought that in your real database those values are null values so i changed my script to this:

and then got this output

.
Please check in you original Database(Not the sample you pro) that if those values are null values or empty string values.
If those are null my solution should work, If those are empty strings than we need to create new solution.


If those are empty strings then try creating this as dimension:-
=aggr(Only({<Error_ID={"=(len(Error_ID)>1)"} >}Error_ID),Product)

[Ahidhar]

Yes , now its working 

[Gabbar]

Mark the question as Solved.