count and total count within straight table

[#1]

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

[JonnyPoole]

any chance of getting a more representative data set than the 5 lines ?

[jagan]

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.

[#4]

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

[#5]

I'll work on creating a more detailed set and attach it here. Need to change values so company data is protected.

[#6]

Total Count is not working, 'Firm Count (w Pdt)' is working fine

[#7]

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!

[jagan]

Hi,

Can you explain in which sheet and which chart to refer?  What you exactly required.

Regards,

Jagan.