Presenting Budget & Forecast Variances

[Anonymous]

Hi

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 ?

[Anonymous]

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.

[Anonymous]

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.