Difference Between Actual and Plan

[#1]

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.

[undergrinder]

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.

[kaushiknsolanki]

Hi,

Have a look at the application.

Regards,

Kaushik Solanki

[jonathandienst]

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)

[reddy-s]

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

[#6]

Thank you.

[#7]

Thank you.

[#8]

Thank you.

[#9]

Thank you. Don't seem to find the app

[sfatoux72]

‌a variante for the chart:

DImension

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

Expression

• Sum(SalesAmount)