count and sales totals for line item combos on invoices

[#1]

I am looking to for some Set Analysis help. qvw attached.

I'd like to calculate the number of invoices and sales with only the following combination line item Product Types.

Invoice Combos	Counts	Total Sales
HW
SW
SVC
HW, SW
HW, SVC
SW, SVC
HW, SW, SVC
Total	102	17153521

Any help would be much appreciated.

Jimmy

[vgutkovsky]

Jimmy,

The expressions would be:

count(distinct {<[Product Type]={'HW','SW','SVC'}>} [Invoice Number])
sum({<[Product Type]={'HW','SW','SVC'}>} [Total Sales])

Regards,

[#3]

Vlad, your expression is not what I am looking for. Your count is resulting in 102 instead of 8.

Here are the totals I am expecting...

Invoice Combos	Counts	Total Sales
HW	29	861,560.74
SW	22	67,746.79
SVC	26	329,124.61
HW, SW	5	654,145.92
HW, SVC	6	141,142.12
SW, SVC	6	75,931.02
HW, SW, SVC	8	557,881.61
Total	102	2,687,532.82

[vgutkovsky]

Hmmm that's a bit more complicated. See attached solution (btw, I think you might have added the sales wrong in your post)

Regards,

[#5]

Thank you. Yes, you got what I asked for but when I loaded in the line items it gave me many more combos than the summarized combos I desired. My fault there. I uploaded line items this time instead of summarized data.

Here is the desired output for the newly uploaded qwv...

Much appreciated,

Jimmy

[vgutkovsky · Accepted solution]

Just add the word "distinct" right after concat( in your calculated dimension. So it would be: aggr(concat(distinct [Product Type],', '),[Invoice Number])

Regards,

[#7]

That worked perfect! Thank you Vlad!