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

[sunny_talwar]

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

[sunny_talwar]

PFA a sample application also.

[anil2185]

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

[sunny_talwar]

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

Best,

Sunny

[ankitaag]

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])

[anil2185]

does the same ankita but total is not correct.

In total sum of all store sales is coming.

Anil

[ankitaag]

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.

[sunny_talwar · Accepted solution]

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

[anil2185]

Sample Data