Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Difference Between Actual and Plan

Hi, I am a newbie trying out Qlik Sense. My dataset consists of month, year, sales amount, actual or plan indicator.  For example,

Month      Year      Sales Amount     Act_Plan

1               2016     1000                    Actual

1               2016     1100                    Plan

2               2016     1200                    Actual

2               2016     1300                    Plan

Queries:

1. How do I create a measure to show the difference between Month Actual less Month Plan?

2. If I want to chart this, how do I show the Sales Amount for Actual and Plan side-by-side?

Thank you.

10 Replies
undergrinder
Valued Contributor II

Re: Difference Between Actual and Plan

Hi Weng,

The easiest way to do that is reshape your data like this:

Month     Year     Amount_Actual     Amount_Plan

1              2016     1000                    1100

You can achive this at load script like that:

Load

     Month

    ,Year

    ,if(Act_Plan='Actual',"Sales Amount") as Amount_Actual

    ,if(Act_Plan='Plan',"Sales Amount") as Amount_Plan

From .......

I'm sure this can be achieved by set expression too, but it's more simple.

G.

Re: Difference Between Actual and Plan

Hi,

Have a look at the application.

Regards,

Kaushik Solanki

MVP
MVP

Re: Difference Between Actual and Plan

For the difference, assuming that year (optionally and month) as dimensions:

=Sum({<Act_Plan = {Actual}>} SaleAmount) - Sum({<Act_Plan = {Plan}>} SaleAmount)

For the chart:

=Sum({<Act_Plan = {Actual}>} SaleAmount)

=Sum({<Act_Plan = {Plan}>} SaleAmount)

reddys310
Honored Contributor II

Re: Difference Between Actual and Plan

Hi Weng,

For the first Query

1) sum({< Act_Plan = {'Actual'} >}[Sales Amount]) - sum({< Act_Plan = {'Plan'} >}[Sales Amount])

2) sum({< Act_Plan = {'Actual'} >}[Sales Amount])

   sum({< Act_Plan = {'Plan'} >}[Sales Amount])

Hope this helps!

Thanks,

Sangram

Not applicable

Re: Difference Between Actual and Plan

Thank you.

Not applicable

Re: Difference Between Actual and Plan

Thank you.

Not applicable

Re: Difference Between Actual and Plan

Thank you.

Not applicable

Re: Difference Between Actual and Plan

Thank you. Don't seem to find the app

sfatoux72
Valued Contributor

Re: Difference Between Actual and Plan

‌a variante for the chart:

DImension

  • calculated dimension :   =MakeDate(Year, Month)
  • Act_Plan

Expression

  • Sum(SalesAmount)