Show Monthly Actual or Budget Data based on Selected Date

[#1]

Hi all,

This is my first post so please forgive me if I get my terminology wrong.

I am trying to create a P&L which has either the Actual or Budget figures depending on the Date Selected (See Below).

Please note that I have had to set up the Max Date in this was as we have Forward Orders.

Assume Date Selected is Sep 2013.

Query 1:

In this example, the calculated dimension works fine but when I apply the same logic to the Expression, it shows only the selected month. Is there a way to exclude the selected month in the If Statement? It's a little contradictory because I want it to acknowledge the selected month but then disregard it.

Query 2:

The Expression outlined above reports back the following:

If the num(vSelectedMonth)<=num(vMaxDate) is True:

The actual data will show for each month (in this case Jul, Aug, Sep) but all other fields are blank (Oct-Jun)

If the num(vSelectedMonth)<=num(vMaxDate) is False:

Every month will show the Budget Data.

Would appreciate any assistance.

Regards

Julie

[jonathandienst]

Julie

I would approach thus problem by including this logic in my data model. By this I mean that I would combine Budget and Actual into a single fact table, and then have a field that indicates that the the number is budget or actual.

          LOAD

               ...

               Month,

               Actual,

               0 As Budget,

               'ACT' As Source,

               Actual As ReportedAmount,

               ...

          FROM ActualData;

          Concatenate

          LOAD

               ...

               Month,

              0 As Actual,

               Budget,

               'BUD' As Source,

               Budget As ReportedAmount,

               ...

          FROM BudgetData;

This way, you can use ReportedAmount for the metric in your table, and the model will supply Actual or Budget according to the date.

Regards

Jonathan

PS: I am assuming a data source in which the budget and actual sources don't overlap.

[#3 · Accepted solution]

Hi

I have this functionality in one of my reports.  The key is to add the actuals and budgets and let the dimension of Month split out the result, i.e. Add the current and previous months actual data to all future budget via Set Analysis.  The expression would be as so;

sum( { ' Month = {"<=$(= MAX([vSelectedMonth]) ) " } >} ' } GLSales)

+

sum( { ' Month = {">$(= MAX([vSelectedMonth]) ) " } >} ' } BudSales)

I think this answers both questions, although please let me know if this is not the case.

Let me know how you get on, if this is the correct answer or if it is merely helpful (or not).

Regards

Steve

[#4]

Hi Jonathan,

Thanks for replying. We have got the actual and budget data combined into the one table. There will always be budget data loaded so the budget amount will never be zero. Unfortunately, we cannot go by zero actual sales either because we do process fwd orders. We currently have sales figures in Qlikview for October and November.

Julie

[#5]

Hi Steve,

Thanks for replying. There was an extra >} in the script but I managed to work out what I needed.

Also, I had to take out the MAX statement for our data as we have FWD Sales and cnnot use the standard Max function.

This was my final expression:

=-sum({<Month = {'<=$(=Date(vSelectedMonth))'}>} GLSales)/1000

- sum({<Month = {'>$(=Date(vSelectedMonth))'}>} BudSales)/1000

Thankyou so much Steve!