Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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;
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
Concatenation will not duplicate the rows on the basis of Actual table rows.
check this -
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
Thanks a lot Neelam for the sample. It made my life much easier to understand the solution.