Expressions in pivot table

[#1]

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

[sunny_talwar · Accepted solution]

May be these:

1) Count (DISTINCT ReceiptPOSDay)

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

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

Output:

[Anonymous]

Hi Maybe  like  this.

[#4]

Hi guys,

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

Regards

Wal