topic Re: count and total count within straight table in QlikView

count and total count within straight table

Tue, 05 Aug 2014 23:06:03 GMT

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

Re: count and total count within straight table

JonnyPoole — Wed, 06 Aug 2014 00:37:33 GMT

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

Re: count and total count within straight table

jagan — Wed, 06 Aug 2014 01:05:36 GMT

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.

Re: count and total count within straight table

Wed, 06 Aug 2014 14:26:24 GMT

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

Re: count and total count within straight table

Wed, 06 Aug 2014 14:47:04 GMT

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

Re: count and total count within straight table

Wed, 06 Aug 2014 15:46:09 GMT

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

Re: count and total count within straight table

Mon, 18 Aug 2014 15:10:13 GMT

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!

Re: count and total count within straight table

jagan — Tue, 19 Aug 2014 04:03:00 GMT

Hi,

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

Regards,

Jagan.