topic Re: Presenting Budget & Forecast Variances in QlikView

Presenting Budget & Forecast Variances

Anonymous — Thu, 17 Nov 2016 11:02:42 GMT

I am trying do something that at first I thought would be easy, but I can't get the solution I am after.

I have attached a qvw with a sample inline data load

My sample data has

A dimension of Area
A dimension of Type with values of Actual, Budget & Forecast
Values for Hours & Money

And in the script I have created an additional Cross Table of the data, as somehow I felt that would help.

The need is to present in a single table a summary of Actuals, Budgets, Forecasts & Variances as columns and Area / Type as Rows formatting the Money cells with £.

My real data has many Areas, more dimensions and many more metrics than just Hours and Money. Plus I need to allow for additional Areas and metrics being added to the source data and the table handling this automatically.

It needs to be a single table as beancounters being beancounters, they will want to export it to Excel.

Either a script or front end solution would be fine

Any suggestions please as my brain has gone numb ?

Re: Presenting Budget & Forecast Variances

MK_QSL — Thu, 17 Nov 2016 11:09:12 GMT

Can you provide excel file showing the final output table based on data provided?

Re: Presenting Budget & Forecast Variances

Anonymous — Thu, 17 Nov 2016 11:23:50 GMT

This is what the result table needs to look like :

Area	Type	Actual	Budget	Variance vs. Budget	Forecast	Variance vs. Forecast
A	Hours	45	22	23	29	16
A	Money	£500	£200	£300	£230	£270
B	Hours	85	45	40	35	50
B	Money	£850	£450	£400	£350	£500

Re: Presenting Budget & Forecast Variances

tresB — Thu, 17 Nov 2016 11:34:19 GMT

May be one UI solution could be like:

If (SecondaryDimensionality()=0,Sum({<CType={Actual}>}Value)-Sum({<CType={Forecast}>}Value), Sum ( Value ))

Re: Presenting Budget & Forecast Variances

Anonymous — Thu, 17 Nov 2016 11:45:13 GMT

Thx - That is certainly progress and cunningly gives the Actual vs.Forecast Variance.

But I can't suss out how to add the Actual vs.Budget Variance.

Re: Presenting Budget & Forecast Variances

rubenmarin — Thu, 17 Nov 2016 12:24:59 GMT

Hi Bill, maybe is a silly answer but CType values seems somewhat fixed, there is the possibility to use different expressions for each column and work dynamism with conditional expressions to show/hide columns?

In example the Budget and budget variance will only show when budget is in the possible values of CType.

All other ideas that come to me are based on a try to fix the variance columns so in any case it will no support new values on CType and it's variances...

unless in the load you create another field for the vertical dimension, a resident load that reads different values of CType and inserts the rows for the variances and a check to know if it's a variance in front-end, the expression can be something like:

If(isVariance, Column(1)-Column(ColumnNo()-1), Sum(Value))

Re: Presenting Budget & Forecast Variances

tresB — Thu, 17 Nov 2016 12:32:51 GMT

May be like attached.

Re: Presenting Budget & Forecast Variances

Anonymous — Thu, 17 Nov 2016 12:38:00 GMT

Those are interesting suggestions.

You are certainly correct that CType only has 3 fixed possible values: Actual, Budget, Forecasts

And I think that could well be exploited

I'll give it a go and see what happens

Re: Presenting Budget & Forecast Variances

sunny_talwar — Thu, 17 Nov 2016 12:51:50 GMT

May be using an Island table and Pick() function?

Re: Presenting Budget & Forecast Variances

rubenmarin — Thu, 17 Nov 2016 12:55:42 GMT

Hi, I also attach the other option, the one that uses another field to create the columns (the disavantage is that it uses another field for vertical dimension to avoid the coreation of new values in CType)

But if the Type values are fixed I will go with the conditional expressions option.

Re: Presenting Budget & Forecast Variances

Anonymous — Thu, 17 Nov 2016 14:48:33 GMT

Well I must say Sunny you certainly are a cunning bunny

Your suggestion is simple yet cunning and works perfectly.

Coincidentally I was interviewing somebody a few days ago and one of my stock interview questions is "What is your favourite Qlik function and why", and the answer I was given was the Pick() function.

Although I must admit it had never occurred to me that it could used like that to add a couple of synthetic Dimension values onto the values held in a field and then be used to pick what expression to use for these synthetic Dimension values.

To close the loop I have added some code to sort the formatting for the money rows so they have the £ sign prefix, tidied it up a bit and re-attached the qvw.

Now I just need to see how this all works with real world data, but I am optimistic.

Many, many thanks.

Re: Presenting Budget & Forecast Variances

Anonymous — Thu, 17 Nov 2016 14:51:53 GMT

Another cunning solution that works. I just wish that I was allowed to mark 2 answers as correct, as both your's and Sunny's are correct answers.