Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
PFA a sample application also.
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
What is the time factor? Would you be able to provide a more representative sample??
Best,
Sunny
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])
does the same ankita but total is not correct.
In total sum of all store sales is coming.
Anil
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.
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
Sample Data