Expressions in pivot table

Wed, 02 Sep 2015 14:56:12 GMT

Hi guys,

I have the following source Excel file (attached the file with all the records):

Store	ReceiptPOSDay	Product	Value	Promotion
STORE A	0070000000120140131	0999103	278,00	N
STORE A	0070000000120140131	0999300	-278,00	N
STORE A	0070000000220140131	0999103	255,00	N

As a result, I'd like to have a Pivot table or a table with the 4 following columns:

Store

# Receipt

# Receipt with discounted records

"Store" will be the dimension as in the Source Excel file

"# Receipt" will be the number of distinct occurrencies of the field "ReceiptPOSDay". I have been able to obtain it with the following expression: COUNT DISTINCT (ReceiptPOSDay)

"# Receipt with discounted records" will be the number of distinct occurencies of the field "ReceiptPOSDay" with at least one record with the field "Promotion" = "Y"

"%" is just "# Receipt with discounted records"/"# Receipt"

For your info,

I've been able to obtain the required data from the Excel source file attached, but with two different pivot tables.

In the first Pivot table (below) I obtained the desired "# Receipt" column

Store	Count (DISTINCT ReceiptPOSDay)
STORE A	435
STORE B	4
STORE C	24
STORE D	31
STORE E	26
Total	519

In the second Pivot table (below) I obtained the desired column "# Receipt with discounted records" (Pls consider only the records with "Promotion" = "Y")

Store	Promotion	Count (DISTINCT ReceiptPOSDay)
STORE A	N	369
STORE A	Y	101
STORE B	N	4
STORE C	N	24
STORE C	Y	4
STORE D	N	29
STORE D	Y	3
STORE E	N	25
STORE E	Y	4

To be more precise this is teh Pivot table/table I'd like to create

Store	# Receipt	# Receipt with discounted records	%
STORE A	435	101	0,23218
STORE B	4	0	0,00000
STORE C	24	4	0,16667
STORE D	31	3	0,09677
STORE E	26	4	0,15385

Can you explain me which expressions to use to have the table / pivot table above?

thanks a lot

Regards

Wal

Re: Expressions in pivot table

sunny_talwar — Wed, 02 Sep 2015 15:00:39 GMT

May be these:

1) Count (DISTINCT ReceiptPOSDay)

2) Count (DISTINCT {<Promotion = {'Y'}>}ReceiptPOSDay)

3) Count (DISTINCT {<Promotion = {'Y'}>}ReceiptPOSDay)/Count (DISTINCT ReceiptPOSDay)

Output:

Re: Expressions in pivot table

Anonymous — Wed, 02 Sep 2015 15:33:48 GMT

Hi Maybe like this.

Re: Expressions in pivot table

Thu, 03 Sep 2015 09:21:56 GMT

Hi guys,

thank you very much for your fast & correct answers!!

Regards

Wal

topic Re: Expressions in pivot table in QlikView

Expressions in pivot table

Re: Expressions in pivot table

Re: Expressions in pivot table

Re: Expressions in pivot table