Pivot table

[anil2185]

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

[mato32188]

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

[ankitaag]

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

[sunny_talwar]

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

Best,

Sunny