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: 
Not applicable

Problem with comparing actual vs budget in bar graph

 

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 !

10 Replies
jonas_rezende
Specialist
Specialist

Verify, if the link below help. Example in data model with tables Sales and Budget.

http://qlikviewcookbook.com/2009/11/understanding-join-and-concatenate/