topic Re: Max Month Sales and Budget in App Development

Max Month Sales and Budget

paulwalker — Wed, 22 Dec 2021 16:36:32 GMT

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!

Re: Max Month Sales and Budget

paulwalker — Sun, 05 Dec 2021 06:35:40 GMT

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.

Re: Max Month Sales and Budget

PrashantSangle — Sun, 05 Dec 2021 06:53:42 GMT

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.

Re: Max Month Sales and Budget

paulwalker — Sun, 05 Dec 2021 07:44:38 GMT

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

Re: Max Month Sales and Budget

PrashantSangle — Sun, 05 Dec 2021 07:57:34 GMT

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

Re: Max Month Sales and Budget

paulwalker — Sun, 05 Dec 2021 08:53:50 GMT

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

Re: Max Month Sales and Budget

MarcoWedel — Sun, 05 Dec 2021 12:07:34 GMT

maybe helpful:

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

Re: Max Month Sales and Budget

paulwalker — Sun, 05 Dec 2021 17:16:50 GMT

any one can help please ?

Re: Max Month Sales and Budget

MarcoWedel — Sun, 05 Dec 2021 20:29:28 GMT

Could you please elaborate on your requirements?

Re: Max Month Sales and Budget

paulwalker — Mon, 06 Dec 2021 00:42:11 GMT

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

Re: Max Month Sales and Budget

paulwalker — Tue, 07 Dec 2021 04:56:18 GMT

did anyone face such issue - please let me know.