topic Pivot table in QlikView

Pivot table

anil2185 — Fri, 24 Apr 2015 10:13:54 GMT

Hi all,

I am trying to do like for like comparison but in pivot table sum of all data is coming.

Sample data :-

Store Sale CY Sale LY

A 100 80

B 50 0 ---- Store opens in current year

C 0 70 Store Closed in current year

D 150 160

Like For Like comparison ---- Output should be ---------

Store Sale CY Sale LY

A 100 80

D 150 160

Total 250 240

But when I try to do it in QV. Pivot table shows total of all stores sales.

Please help.

Anil

Re: Pivot table

sunny_talwar — Fri, 24 Apr 2015 10:25:11 GMT

Use a calculated dimension: =If([Sales CY] <> 0 and [Sales LY] <> 0, Store) and supress null and use the following two expressions: Sum([Sales CY]) and Sum([Sales LY]) and it will get you to where you want to go.

Best,

Sunny

Re: Pivot table

sunny_talwar — Fri, 24 Apr 2015 10:25:53 GMT

PFA a sample application also.

Re: Pivot table

anil2185 — Fri, 24 Apr 2015 10:38:37 GMT

Thanks for reply sunny.

But we can't put =If(sum([Sales CY]) <> 0 and sum([Sales LY]) <> 0, Store) in calculated dimension.

Because there is time factor also in the expression.

Anil

Re: Pivot table

sunny_talwar — Fri, 24 Apr 2015 10:50:18 GMT

What is the time factor? Would you be able to provide a more representative sample??

Best,

Sunny

Re: Pivot table

ankitaag — Fri, 24 Apr 2015 10:57:24 GMT

Hi,

Wite the two expression as

=if(sum([Sale CY])<>0 and sum([Sale LY])<>0,[Sale CY])

and

=if(sum([Sale CY])<>0 and sum([Sale LY])<>0,[Sale LY])

Re: Pivot table

anil2185 — Fri, 24 Apr 2015 11:19:59 GMT

does the same ankita but total is not correct.

In total sum of all store sales is coming.

Anil

Re: Pivot table

ankitaag — Fri, 24 Apr 2015 11:22:57 GMT

Can you attach a sample data..

I have just 1 dimension in pivot table so no total is diplayed

If I use straight table then the total is correct.

Re: Pivot table

sunny_talwar — Fri, 24 Apr 2015 11:27:06 GMT

Ankita‌ you can get the totals now also (within a pivot table) you need to use this expression to get correct totals:

=Sum(Aggr(If(Sum([Sale CY])<>0 and Sum([Sale LY])<>0,[Sale CY]), Store))

=Sum(Aggr(If(Sum([Sale CY])<>0 and Sum([Sale LY])<>0,[Sale LY]), Store))

and you need to check partial sum on presentation tab for Store and you will see the correct totals.

HTH

Best,

Sunny

Re: Pivot table

anil2185 — Fri, 24 Apr 2015 11:32:57 GMT

Sample Data

Re: Pivot table

mato32188 — Fri, 24 Apr 2015 11:35:24 GMT

Hi,

you can try to set it like

dimensions: Store

expressions: If(sum(Sale CY)=0, null(), sum(Sale CY) )

If(sum(Sale LY)=0, null(), sum(Sale LY) )

and suppress null values in presentation tab

Re: Pivot table

ankitaag — Fri, 24 Apr 2015 11:45:56 GMT

If you try with

=Sum(Aggr(If(Sum([Sale CY])<>0 and Sum([Sale LY])<>0,[Sale CY]), Store))

=Sum(Aggr(If(Sum([Sale CY])<>0 and Sum([Sale LY])<>0,[Sale LY]), Store))

You get the correct total

Re: Pivot table

sunny_talwar — Fri, 24 Apr 2015 12:10:18 GMT

I don't see any time dimension in this sample as well? What time dimension were you talking about?

Best,

Sunny