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
