Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there,
II may have missed it but I couldn’t find a solution for my need to have a bar graph that will compare actuals sales to budgeted sales.
Here is the situation:
I have a table with invoice information (invoice date, amount…). In the load process I have created Year, Month, YearMonth fields with no problem.
I have a table with budget information. I only have Year, Month and amount as fields. I created a BudgetDate with MakeDate(YearBudget,MonthBudget,).
I need to create a bar graph that will present invoice sum as well as budgeted sales f by YearMonth. Sounds easy…
I have create the bar graph with sum of invoice by YearMonth with no problem. I use the dimension YearMonthInvoice and I have a measure Sum of sales which is Sum({$<[YearInvoiceDate]= >}[Job Contract Amount]) in order to present full data even if a year selection is done.
I also have a measure Budgeted Sales which is causing problem.
If I do sum({<YearBudget={'$(=max(YearInvoiceDate))'}>}MontantBudget), I get the same amount of budget for each month.
If I do sum({<MonthYearBudget={'$(=max(MonthYearInvoice))'}>}MontantBudget), I don’t get any data for budget.
I noticed that by changing my dimension from YearMonthInvoice to YearMonthBudget, the budget part is working fine but the actual sales goes wrong.
I suspect I might have to create a relationship between Budget and Sales dates… or use a master calendar. But I also have many other dates (booking, delivery, closure…) which I may need to use to do comparison and I need to understand how to manipulate dates better.
You help would be greatly appreciated. To help understanding the situation, I have attached the apps. Problem is in the first sheet named Global.
Thanks in advance !
Verify, if the link below help. Example in data model with tables Sales and Budget.
http://qlikviewcookbook.com/2009/11/understanding-join-and-concatenate/