Sum of a field based on two unique fields.

[#1]

Hi all,

Consider the following:

INPUT
Store	Buyer	Dept	Unit Retail	Store Retail	Unit Sales
3145	Marry	5526	5	1	4
3146	Marry	5526	5	5	10
3145	Marry	5526	5	5	18
3145	Marry	5527	8	5	3000
3147	Marry	5527	8	5	428
OUTPUT
Buyer	Dept	Unit Retail	Store Retail	Unit Sales
Marry	5526	5	1	4
Marry	5526	5	5	28
Marry	5527	8	5	3428

On two unique fields Unit Retail and Store Retail calculate the Unit Sales sum.

Ex. Row 2 and 3 are identical when considering Unit Retail and Store Retail, there fore the total for this combination 5 and 5 should be 28 (10 + 18) and so on.

One idea was to create unique field for each row using "UnitRetail - StoreRetail" and store it along with the Unit Sales. Then in the table display unique entries for "UnitRetail - StoreRetail" along with sum(UnitSales).

Does anyone have a better idea for this issue?

Best regards,

T

[IAMDV]

Hi T,

Have you considered using the calculated dimension on UnitRetail and StoreRetail? This is kind of similar technique as you thought but you don't have to create additional field.

I mean something like this in your dimension : [Unit Retail] & [Store Retail]

And you can change the name of the calculated dimension.

I hope this helps!

Good luck!

Cheers - DV

[#3]

Hi,

Can you please elaborate further. I am somewhat of a noob to Qlikview.

Thank you,

T

[IAMDV · Accepted solution]

Hi T,

Please find attached QV document. Please look at the dimension tab and I have used calculated dimension.

I hope this helps!

Cheers - DV

[#5]

Thank you for the response. I will attempt to use the same logic and provide you feedback.

Thanks,

T