Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ?
May be using an Island table and Pick() function?
Can you provide excel file showing the final output table based on data provided?
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 |
May be one UI solution could be like:
If (SecondaryDimensionality()=0,Sum({<CType={Actual}>}Value)-Sum({<CType={Forecast}>}Value), Sum ( Value ))
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.
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>
May be like attached.
Those are interesting suggestions.
You are certainly correct that CType only has 3 fixed possible values: Actual, Budget, Forecasts
I'll give it a go and see what happens
May be using an Island table and Pick() function?
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.