Cumulated Budget without accumulation option

pacoli2013 — Wed, 05 Apr 2017 15:03:19 GMT

Hello,

I have a Problem with designing a Barchart with bars for Sales and a line for Budget.

I have attached a Document as example.

At the moment I use an Excelfile: I have a cumulated Budget per week, it’s from a Excelfile where the daily budget are cumulated in to weeks Budget : so per week one Budget.

But we have a DataWareHouse where the Daily Budgets and Sales are stored.

Sales are on daily bases and in the graphic cumulated.

In the document I have a graphic with the following dimension and expressions:

Dimension : =Week &'-' & DayShortName

Expression for Sales: = Sum({$ < Month= {$(= max(Month))}> } Value)

Expression for Budget: = Sum(ECOPlanCum)

Expression for the Difference: = Sum(ECOPlan) - Sum({$ < Month= {$(= max(Month))}> } Value)

When there is one week selected, the graphic should show cumulated sales per day of that week (one bar per day), the (cumulated) Budget of that week and on every bar the calculated difference between Budget and Sales for that day.

When more than one week is selected or the weeks belonging with a month (when a month is selected):

Sales should start with the Sales of the first day of the first week selected and then cumulated till the last day of the last week selected

Budget should start with the Budget belonging to the first week selected, for the second week Budget should be Budget of the first week plus Budget of the second week, for the third week Budget should be Budget of the first week plus Budget of the second week plus Budget of the third week, and so on.

When using the Budgets from the DWH, I need to accumulate the budgets without using the accumulation option from Qlikview because the line will not be horizontal during the week.

I hope someone can help me with the following questions:

I’m using Week &'-' & DayShortName as Dimension, is it possible to use them separate, so two dimensions on the horizontal axe.
Is my expression for Sales correct or is there a better (Qlikview-rules following) expression.
How should the Budget-expression be rebuild when only budget on daily bases is available (from DWH): so cumulate days per week and then cumulate selected week
The third expression should shows the value on top of the bars, and when Sales is greater than Budget no value should be displayed. Now it is as a diamond displayed.

Watching my Document and Graphic, I think I’m very close to a good solution but my knowledge of Set Analysis is too little to clear the question 3 and 4 (the Budget Problem and Value Problem)

Thanks in advance

Court

topic Cumulated Budget without accumulation option in QlikView

Cumulated Budget without accumulation option