Show full year dimension in graph but only plot where there is an Actual

[keithlawrence]

I need some help with plotting a graph in an app that I have produced if anyone can be of assistance.

I'm creating an app for the Finance department that will show variances of Actual to Budget. We work from periods and not specific dates as this is the Finance world. I have budgets for all 12 periods in the year but only actuals up to the point we are reporting. I have a master calendar linked to my data that has all the periods and years.

My data looks like this:

Period     Actual     Budget

1             100          150

2             110          145

3             105          150

4             100          140

5                            130

6                            145

7                            120

8                            135

9                            140

10                          130

11                          150

12                          145

The user has the option to select any period they want and I don't want the graph to change with that selection. I currently have this as my expression:

sum({1}[Actual])-sum({1}[Budget]).

The dimension I'm using is from the Master Calendar.

Works fine and shows me all the months. My problem is that I'm seeing a huge jump from period 5 on-wards because there is no Actual value. I want the graph to stop plotting at period 4 but still show me all 12 periods. In Excel I would have the values as N/A. I basically want this but in QlikView.

One suggestion made to me was to have the variance added into the load but my values were not correct when I compared it to the raw data.

The load script looked like this:

LOAD	[Actual]-[Budget] as 'Actual Budget Variance',
*
;

LOAD [Period],

     [Actual],

     [Budget]

FROM

etc......

I could see no reason why my values where not being correctly calculated in the load script so abandoned the idea.

Any help or ideas would be greatly welcomed!

Thanks

Keith

[sunny_talwar]

Try this expression:

=If(Sum({1}Actual) = 0, 0.001, Sum({1}[Actual])-Sum({1}[Budget]))

I am not sure if it will work for 100%, but give it a try.

Best,

Sunny

[jonathandienst]

This will return an null for 0 sales:

=If(sum({1} Actual) <> 0, sum({1} Actual) - sum({1} Budget))

[keithlawrence]

Thanks Sunny but my graph comes back with 'no data to display'.

Thanks Jonathan but my graph is just plotting periods 1-4 with that expression.

[sunny_talwar]

Made a slight change in the expression:

Try this:

=If(Sum({1}Actual) = 0, 0.001, Sum({1}[Actual])-Sum({1}[Budget]))

[keithlawrence]

Thanks Sunny.

It's a step closer but now I have an additional line on my graph all plotting at zero. My line I want to plot also tracks zero through the periods I don't have Actuals.

Maybe it's not possible to stop a line part way through the graph with Qlik?

[sunny_talwar]

In the background color for your expression

You can use this:

=If(Sum({1}Actual) = 0, white())

it will then make the line white after 4th month

[sunny_talwar]

Please find attached a sample application.

Best,

Sunny

[keithlawrence]

Hi Sunny,

I like the idea and in theory it works, I prefer to use ARGB (0,0,0,0) to make it transparent due to me showing gridlines on my graph. The line does continue to period 5 at zero though unfortunately.

I've had a rethink and will probably change my graph to show the last 12 months of data with the current period being the furthest on the right.

Thanks for the help anyway.

Regards

Keith

[sunny_talwar]

No problem.

I wish I could have been of a better help, but I am sure you will find a solution of your liking.

Best,

Sunny