Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Community,
I have two tables, Revenue and Budget, I have to calculate MTD for both Revenue and Budget
My Revenue max month November and the budget month is December,
But what is the maximum month in Revenue, the maximum month should show the same even in the budget.
(if it is oct in Revenue, Budget should be OCT value)
Revenue | Budget | |||
MonthYear | Sales | Budget MY | Sales | |
Jan-21 | 200 | Jan-21 | 100 | |
Feb-21 | 300 | Feb-21 | 320 | |
Mar-21 | 400 | Mar-21 | 540 | |
Apr-21 | 500 | Apr-21 | 760 | |
May-21 | 600 | May-21 | 980 | |
Jun-21 | 700 | Jun-21 | 1200 | |
Jul-21 | 800 | Jul-21 | 1420 | |
Aug-21 | 789 | Aug-21 | 1640 | |
Sep-21 | 235 | Sep-21 | 1860 | |
Oct-21 | 234 | Oct-21 | 2080 | |
Nov-21 | 790 | Nov-21 | 2300 | |
Dec-21 | 2520 |
Based on user selection value should be change..
and YTD also should be same..
Thanks in Advance!
I need to restrict based on user selection.
Lat say-- Revenue Month Nov = Budget Month Should be Nov (If it is Oct Revenue, Budget should be Oct)
If user select July, Revenue and Budget should be July
and YTD should be Jan and July for both Revenue and Budget.
Can you post what have you tried..???
1: Create island calendar
2: for Budget YTD
Sum({<common_date={">=$(=yearstart(max(common_date)))<=$(=max(common_date))"}>}Budget)
3: for Revenue YTD
Sum({<common_date={">=$(=yearstart(max(common_date)))<=$(=max(common_date))"}>}Revenue)
Note: Please be careful with date format, without proper date format set analysis won't work
max() return date in numeric format.
Thanks for reply.. in my scenario
MTD Revenue = 790 and YTD Revenue = 5548
MTD Budget = 2300 and YTD Budget = 13200
I have revenue till Nov, so Budget should be till Nov (If it is Revenue Oct, Budget should be Oct )
PFA..
I don't have license qlik view install in my machine so not able to open qvw file
this case you do in backend.
Revenue:
Load * from Revenue_Table;
Budget:
Load * from Budget_Table where exists(MonthYear,[Budget MY]);
Regards,
Prashant Sangle
Yes, but some other case I have to show full year budget.
May be I have to restrict in Set Analysis.
Please find the Script..
Revenue:
LOAD *,
DATE#(MonthYear1, 'MMM-YY') as MonthYear,
NUM(MONTH(DATE#(MonthYear1, 'MMM-YY'))) as Month,
'Revenue' as Flag;
LOAD * INLINE [
MonthYear1, Sales
Jan-21, 200
Feb-21, 300
Mar-21, 400
Apr-21, 500
May-21, 600
Jun-21, 700
Jul-21, 800
Aug-21, 789
Sep-21, 235
Oct-21, 234
Nov-21, 790
];
BUDGET:
LOAD *,
DATE#(MonthYear1, 'MMM-YY') as MonthYear,
MONTH(DATE#(MonthYear1, 'MMM-YY')) as Month,
'Budget' as Flag;
LOAD * INLINE [
MonthYear1, Sales
Jan-21, 100
Feb-21, 320
Mar-21, 540
Apr-21, 760
May-21, 980
Jun-21, 1200
Jul-21, 1420
Aug-21, 1640
Sep-21, 1860
Oct-21, 2080
Nov-21, 2300
Dec-21, 2520
];
any one can help please ?
Could you please elaborate on your requirements?
I have Revenue and Budget Sales..
both(Revenue and Budget) MTD should be - Revenue latest month (Highlighted in the below table)
and YTD should be Jan-Nov in both tables... (If we have till Sep in Revenue.. both YTD should be Jan-Sep)
OutPut Should be, based on user selection value should be change
MTD Revenue = 790 and MTD Budget = 2300
YTD Revenue = 5548 and YTD Budget = 13200
Revenue | Budget | |||
MonthYear | Sales | Budget MY | Sales | |
Jan-21 | 200 | Jan-21 | 100 | |
Feb-21 | 300 | Feb-21 | 320 | |
Mar-21 | 400 | Mar-21 | 540 | |
Apr-21 | 500 | Apr-21 | 760 | |
May-21 | 600 | May-21 | 980 | |
Jun-21 | 700 | Jun-21 | 1200 | |
Jul-21 | 800 | Jul-21 | 1420 | |
Aug-21 | 789 | Aug-21 | 1640 | |
Sep-21 | 235 | Sep-21 | 1860 | |
Oct-21 | 234 | Oct-21 | 2080 | |
Nov-21 | 790 | Nov-21 | 2300 | |
Dec-21 | 2520 |