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

Bar chart with actual margin to date then forecast margin

I'm probably making this more complicated than it needs to be but I can't see it anywhere. I'm trying to produce a chart for the year that uses actual figures until the selected month and then the forecast beyond that to year end. The expression I have a the moment is:

                        =If(????,Only({<Month>}MarginForecast),Only({<Month>}KPIMargin))

And I'm struggling with what to put in the If statement.

The data looks like this (dummy data):

   

MonthYearTotal Stream Revenue PlannedTotal Stream Revenue ForecastTotal Stream Revenue ReforecastTotal Stream Revenue ActualTotal Stream Costs PlannedTotal Stream Costs ForecastTotal Stream Costs ReforecastTotal Stream Costs
Jan-18500500500450400400400423
Feb-18500500400400400400400497
Mar-18500500500350400400400400
Apr-18500400400 400200400
May-18500400400 400200200
Jun-18500450450 400200200
Jul-18500300300 400200200
Aug-18500300300 400200200
Sep-18500300300 400200200
Oct-18500300300 400200200
Nov-18500300300 400200200
Dec-18500300300 400200200

The relevant bit of the script is:

[Total Stream Revenue Planned] - [Total Stream Costs Planned] as MarginPlanned,

[Total Stream Revenue Forecast]-[Total Stream Costs Forecast] as MarginForecast,
[Total Stream Revenue Reforecast]-[Total Stream Costs Reforecast] as MarginReforecast,
[Total Stream Revenue Actual]-[Total Stream Costs] as KPIMargin,
[Total Stream Revenue Actual] as Revenue,
[Total Stream Revenue Planned] as RevenuePlanned,
[Total Stream Revenue Forecast] as RevenueForecast,
[Total Stream Revenue Reforecast] as RevenueReforecast,
[Total Stream Costs] as Costs,
[Total Stream Costs Planned] as CostsPlanned,
[Total Stream Costs Forecast] as CostsForecast,
[Total Stream Costs Reforecast] as CostsReforecast,

If a month is selected I get:

I can get this with no month selected (which is what I want it to look like), but it messes up 17 other items on the dashboard:

Does anyone have any ideas?

Thank you

0 Replies