Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Presenting Budget & Forecast Variances

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 ?

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

11 Replies
MK_QSL
MVP
MVP

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

Anonymous
Not applicable
Author

This is what the result table needs to look like :

      

AreaTypeActualBudgetVariance vs. BudgetForecastVariance vs. Forecast
AHours4522232916
AMoney£500£200£300£230£270
BHours8545403550
BMoney£850£450£400£350£500
tresesco
MVP
MVP

May be one UI solution could be like:

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

Capture.PNG

Anonymous
Not applicable
Author

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.

rubenmarin

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...

<mode brainstorming ON>

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))

<mode brainstorming OFF>

tresesco
MVP
MVP

May be like attached.

Capture.PNG

Anonymous
Not applicable
Author

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

sunny_talwar

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

Capture.PNG

rubenmarin

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.