Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Expressions in pivot table

Hi guys,

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

     

StoreReceiptPOSDayProductValuePromotion
STORE A00700000001201401310999103278,00N
STORE A00700000001201401310999300-278,00N
STORE A00700000002201401310999103255,00N

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

  

StoreCount (DISTINCT ReceiptPOSDay)
STORE A435
STORE B4
STORE C24
STORE D31
STORE E26
Total519

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

  

StorePromotionCount (DISTINCT ReceiptPOSDay)
STORE AN369
STORE AY101
STORE BN4
STORE CN24
STORE CY4
STORE DN29
STORE DY3
STORE EN25
STORE EY4

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

    

Store# Receipt# Receipt with discounted records%
STORE A4351010,23218
STORE B400,00000
STORE C2440,16667
STORE D3130,09677
STORE E2640,15385

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

thanks a lot

Regards

Wal

1 Solution

Accepted Solutions

Re: Expressions in pivot table

May be these:

1) Count (DISTINCT ReceiptPOSDay)

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

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


Output:

Capture.PNG

3 Replies

Re: Expressions in pivot table

May be these:

1) Count (DISTINCT ReceiptPOSDay)

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

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


Output:

Capture.PNG

maverjannet
Valued Contributor

Re: Expressions in pivot table

Hi Maybe  like  this.

Captura1.JPGCaptura2.JPG

Captura3.JPGCaptura4.JPG

Captura 5.JPG

Not applicable

Re: Expressions in pivot table

Hi guys,

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

Regards

Wal