topic Re: Actual Vs Plan in Pivot Table in QlikView

Actual Vs Plan in Pivot Table

felipe_oliveira — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Actual Vs Plan in Pivot Table

robert99 — Tue, 18 Sep 2018 20:26:59 GMT

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

Re: Actual Vs Plan in Pivot Table

Anonymous — Wed, 19 Sep 2018 08:49:54 GMT

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;

Re: Actual Vs Plan in Pivot Table

felipe_oliveira — Wed, 19 Sep 2018 13:38:04 GMT

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

Re: Actual Vs Plan in Pivot Table

neelamsaroha157 — Wed, 19 Sep 2018 13:58:15 GMT

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

check this -

Re: Actual Vs Plan in Pivot Table

robert99 — Wed, 19 Sep 2018 16:11:15 GMT

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

Re: Actual Vs Plan in Pivot Table

felipe_oliveira — Wed, 19 Sep 2018 19:33:06 GMT

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