Actual Vs Plan in Pivot Table

[felipe_oliveira]

Hi Masters,

I have 2 tables:

Table1

City	Actual_$	Date	Quarter	Year	Key#
Miami	30	25/03/2018	Q1	2018	Miami|Q12018
Miami	20	15/04/2018	Q2	2018	Miami|Q22018
Miami	25	22/05/2018	Q2	2018	Miami|Q22018
Miami	15	13/08/2018	Q3	2018	Miami|Q32018
Miami	25	23/08/2018	Q3	2018	Miami|Q32018
Boston	30	13/02/2018	Q1	2018	Boston|Q12018
Boston	15	21/03/2018	Q1	2018	Boston|Q12018
Boston	25	15/05/2018	Q2	2018	Boston|Q22018
Boston	10	29/05/2018	Q2	2018	Boston|Q22018
Boston	15	22/08/2018	Q3	2018	Boston|Q32018

Table2

City_Plan	Plan_$	Quarter_Plan	Year_Plan	Key#
Miami	60	Q1	2018	Miami|Q12018
Miami	50	Q2	2018	Miami|Q22018
Miami	70	Q3	2018	Miami|Q32018
Miami	70	Q4	2018	Miami|Q42018
Boston	60	Q1	2018	Boston|Q12018
Boston	50	Q2	2018	Boston|Q22018
Boston	70	Q3	2018	Boston|Q32018
Boston	70	Q4	2018	Boston|Q42018

The link between the 2 tables is the Key# (City & '|' & QuarterYear).

In a Pivot table I would like to compare the Total Year Actual vs Total Year Plan

For example, Miami has the Year Actual = $115 (30+20+25+15+25)  and the Year Plan is $250 (60+50+70+70)

In the Pivot table my dimension is City from Table 1

For the total year Plan for Miami I have tried the following set analysis:

=Sum(Total<City>Plan_$)

=Sum({1<Year_Plan={2018},City_Plan=p(City)>}Plan_$)

=Sum({1<Year=,Quarter=,City=p(City_Plan)>}Plan_$)

None of the above worked. It shows as Total Plan for Miami $180 (sum of Q1 + Q2 + Q3). Even though Q4 does not exist in Table 1, I would like to include Q4 in the Total Plan for the year.

Please help me

Thanks

Felipe

[robert99 · Accepted solution]

Hi Felipe

I always concatenate a budget to an actual (I don't join). With a set analysis field built in (say 'Budget' as GLBudgetActual and 'Actual' as GLBudgetActual)

And often add a date to the budget if its missing one. So it fits into the correct quarter if a calendar is done based on the date

As I had issues by joining when starting out

[Anonymous]

Hello,

If you want to keep both table, then you can create a Link Table for all the common fields.

Add below script, then use City_L in Pivot Table dimension, and just use Sum(Actual_$) & Sum(Plan_$) in expression.

LinkTable:

LOAD City as City_L, 

     Quarter as Qtr_L,

     Year as Year_L,

     Key#

RESIDENT Table1;

Concatenate(LinkTable)

LOAD City_Plan as City_L,  

     Quarter_Plan as Qtr_L, 

     Year_Plan as Year_L,

     Key#

RESIDENT Table2; 

[felipe_oliveira]

Hi Robert,

After concatenating, how can I avoid duplication of Plan_$ since in the Actual table there are more rows within the same quarter.

Please advise.

Regards

Felipe

[neelamsaroha157]

Concatenation will not duplicate the rows on the basis of Actual table rows.

check this -

[robert99]

Hi Felipe

Why would it duplicate?

But even if both revenue columns had the same name (say Revenue). Just use set analysis as explained above

For actual

Sum({$<GLBudgetActual = {Actual}>} Actual_$)

For Plan (I called it budget)

Sum({$<GLBudgetActual = {Budget}>} Plan_$)

Either filter as required. Or build in additional set analysis

NB Of course you would change the field names of plan to match with Actual (except maybe Plan_$) eg City_Plan to City. In this way filtering is easier

[felipe_oliveira]

Thanks a lot Neelam for the sample. It made my life much easier to understand the solution.