Max Month Sales and Budget

[paulwalker]

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!

[paulwalker]

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.

[PrashantSangle]

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.

[paulwalker]

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..

[PrashantSangle]

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

[paulwalker]

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
];

[MarcoWedel]

maybe helpful:

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

[paulwalker]

any one can help please ?

[MarcoWedel]

Could you please elaborate on your requirements?

[paulwalker]

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