Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the following data table loaded in my qlikview doc. The actual table has many rows with lots of products and category.
Firm Name | Firm Type | Product Name | Product Category | Firm Size | Revenue | ||
---|---|---|---|---|---|---|---|
RAM LLC | A | Coffee | Drug | 5 | $500 | ||
SRAM LLC | B | Air | Nature | 2 | $199 | ||
DDR LLC | C | Air | Nature | 1 | $199 | ||
SDR LLC | D | Poppy Seeds | Grain | 3 | $82 | ||
LSD LLC | D | Love | Mammal | 1 | $10 |
I'm trying to create a view that shows product penetration:
Firm Type | Product Name | Firm Size | Product Category | Average Rev | Firm Count (w Pdt) | Firm Count Total | Penetration |
---|---|---|---|---|---|---|---|
A | Coffee | 5 | Drug | $250 | 1 | 1 | 100% |
A | Coffee | 4 | Drug | $100 | 3 | 3 | 100% |
A | Coffee | 3 | Drug | $75 | 4 | 7 | 58% |
A | Coffee | 2 | Drug | $50 | 10 | 15 | 66% |
A | Coffee | 1 | Drug | $25 | 15 | 50 | 30% |
Firm size indicates the class a firm belongs to based on their volume (5 - highest volume , 1 - lowest volume)
Firm Count (w Pdt) - Distinct count of (firms of a particular Firm Type and Firm Size Bucket that bought the product, coffee in this example)
Firm Count Total - Distinct count of (firms in a particular Firm Type and Firm Size bucket that bought any product in the Product Category that "Coffee" belongs to, in this example it is "Drug")
Firm Count (w Pdt) = Firm Type + Firm Size + Product Category + Product
Firm Count Total = Firm Type + Firm Size + Product Category
I built a staright table and Im struggling with the counts, have tried a lot (set analysis counts, straight counts, aggr() counts)
count ( distinct [Firm Name])
Count ( distinct {<[Product Line]>} {<[Firm Size]>} [Firm Name])
It would be great if someone could help. Thanks!
Please let me know if you have any additional information about this problem
any chance of getting a more representative data set than the 5 lines ?
Hi,
Please check below expressions
For Count:
count ( distinct [Firm Name])
For Total Count:
count ( TOTAL distinct [Firm Name])
For Total Count by Product Line and Firm Size
Count ( TOTAL <[Product Line], [Firm Size]>distinct [Firm Name])
Hope this helps you.
Regards,
jagan.
Hi Jagan,
thanks for the reply. I tried your expressions and they did not work for me. Not sure if its because of the staright table
I'll work on creating a more detailed set and attach it here. Need to change values so company data is protected.
Total Count is not working, 'Firm Count (w Pdt)' is working fine
Hi Jagan/Jonathan,
Attached is a detailed data set and also my qvw file. Please let me know if there is a way I could get these counts to work.
Thanks!
Hi,
Can you explain in which sheet and which chart to refer? What you exactly required.
Regards,
Jagan.