Actual vs Budget

[#1]

Hello there,

first I apologize for asking something that has been already asked, I have search this forum but I cannot find my answer. My problem is that I have created a Key (concatenating Month & Year & DemandEntity) everything works my issue is when comparing Actual vs Budget, if there are no actuals I cannot find a way to display 0 and still show the budget for those months, I need to be able to display this on a graph, I need to show all months and a bar to show actuals and a line to show budget. I was able to create the graph but I cannot show budget for the entire year because I do not have sales for the future yet. How can I accomplish this?

Thank you,

Hugo

[chiru_thota]

HI Hugo.

You can achieve by appending some dummy data(0 or 0.01) into your data model where ever you don't have data.

So that it will come into your graph.

You can use inline if data is less .otherwise use loop to by talking distinct dimensions.

Ex:

ISLAND_TREND_TIMEPERIOD:

LOAD * INLINE [

Trend_TP
1001,
1002,
1003,

];

//********************************* Non Retail ***********************************************

Let table_name = QVDPath & 'QVDname' & OSORG_CD & '.qvd';

TEMP_NR_NULL_ZERO:
LOAD distinct  
RETAIL_IND,
SORG_ID,
FIN_BRND_ID,
MKT_PID,
CID,
SLS_SUBCAT_CD


FROM
$(table_name)
(qvd)  ;




NR_NULL_ZERO:
NoConcatenate
LOAD null() as [%RowID]
AutoGenerate 0;


LET currField = 'Trend_TP';
LET recordNo = 1;
DO WHILE recordNo < FieldValueCount('$(currField)') + 1;
LET TIme = FieldValue('$(currField)', $(recordNo));


Concatenate (NR_NULL_ZERO)

LOAD
RETAIL_IND,
SORG_ID,
MKT_PID,
FIN_BRND_ID ,      
CID,
$(TIme) as TIM_PRD_ID,
SLS_SUBCAT_CD,
0.01 as SLS_DLR_AMT
Resident TEMP_NR_NULL_ZERO;


LET recordNo = recordNo + 1;
LOOP



drop table TEMP_NR_NULL_ZERO;

drop field  [%RowID];

[Alejandro_Hernández]

without seeing your data model I'm pretty sure you can solve this by concatenating your sales/budget data vs. joining.

[#4]

Hi,

place your keys into separate LINK TABLE this is just a regular table which will have all keys for all dates. Then link it to your facts (budget and actuals)/

If there is no budget for specific key in facts it will produce 0 or null based on your settings.

[#5]

Thank you Chiranjeevi,

I would really like to try your suggestion but I am still new to qlikview and I don’t quite understand about inline or loop; where in the code would I use inline? From your sample code I will have to replace table names and field names to the ones I have, correct?

Thanks,

Hugo

[Peter_Cammaert]

I favor Link Tables as well, because they are capable of making your application survive all sorts of modifications-extensions-reversals that your average business user may ask you to do in a hurry. And you're probably fighting mixed granularity, unwanted filtering through fact tables and multiple identical dimensions or soon you will, issues that are handled well by Link Tables.

However, the concept of a Link Table and where to use it may not ring a bell yet. Therefor I suggest you read one of the great posts by HIC, this time on Generic keys

In the accompanying PDF document, examples 2 and 3 ("Actuals and Budget") explain in detail the solutions that Alejandro and Ersen suggested before me. Enjoy an enlightening read.

Peter

[#7]

Thank you Peter,

This is certainly more complicated than I thought but the document has lots of information. I will give this a try right now.

Thanks,

Hugo

[Peter_Cammaert]

Well, it may seem complicated but that's mostly because of the wide range of situations it applies to.

If you haven't got the time to analyse this pile of background information, you can read it later on.

Just provide us with a little insight into your situation by posting a sample document, or a screenshot of your current data model. Otherwise it will be difficult to provide suggestions that are immediately useful to you.

Peter

[#9]

Yes thank Peter, I think it is the solution to go for, I need to understand it to be able to change according to my dataset.

Here is a sample of my tables(my actual are on the Master table and Budget on the Budget (and Budget_SalesFlash). I created Keys to join the tables):

My biggest issue is with the graph, since it won’t display the entire year (month by month) because I have no sales for the future. Right now it will only compare Jan-Mar, but what my company ones is to see the entire budget and 0’s if there are no actual.

Thanks,

Hugo

[Peter_Cammaert]

Oh, but you're almost there! As far as I can see, your MASTER table is created based on the data from your sales. It is almost a Link Table, except that some dimension values are probably missing. It contains a month and year field, right?

In QlikView, Link Tables are used to connect two or more Facts tables to each other. In cases of missing date values or varying granularity, the construction of a Link Table usually starts from a Master Calendar (search the community for a wealth of examples and explanations), meaning that you select either a fixed starting/ending date or you determine what is the earliest/latest budget/sales date, add some margin (yearstart and yearend are common) and generate a calendar with dates and offspring (month, yearmonth, day, year, quarter etc.). Then you multiply this calendar via cartesian product with dimension values that occur in your different keys (like product, region or whatever you're using). Next, from these lists of different fields, you create all possible key values to the different fact tables and add them to your Link Table. Separate the additional data in MASTER from the keys, and attach that data to the Link Table as well.

After all these changes, you will have:

• an uninterrupted timeline, whether budget or actuals are available or not
• the possibility to group different facts according to different rules, for example sales are date based, and budget values are month-based.
• an association between dimension values like product, region, month, year etc. and budget/actuals,even if one of them is missing

If you concatenate Sales to Budget, extract the calendar from your data and you get the same solution. But then you'll have an entirely different (simpler) data model and probably more work to do.