Set Analysis - YTD & MTD

[Anonymous]

Hi

Below is a sample Sales data.  Actuals Sales is up to Sep 2017, however Budgeted Sales goes up to June 2020.

How do I create an expression so that the Max Financial Year and Max Month is current (with so selections made)

So for example, its currently Sept 2017.  So,

• Financial Year = 2018
• Month = Sep 2017
• Period_ID = 15

and say if currently it is  Nov 2018, then

• Financial Year =  2019
• Month = Nov 2018
• Period_ID = 29

Thank you

Kind regards

Nayan

YearMonth	Month	Period_ID	Financial Year	Actual Sales	Budgeted Sales
201607	Jul	1	2017	600	400
201608	Aug	2	2017	500	200
201609	Sep	3	2017	500	900
201610	Oct	4	2017	400	800
201611	Nov	5	2017	300	1000
201612	Dec	6	2017	1000	900
201701	Jan	7	2017	1000	200
201702	Feb	8	2017	600	200
201703	Mar	9	2017	400	800
201704	Apr	10	2017	600	100
201705	May	11	2017	500	900
201706	Jun	12	2017	800	700
201707	Jul	13	2018	200	300
201708	Aug	14	2018	100	600
201709	Sep	15	2018	100	600
201710	Oct	16	2018		300
201711	Nov	17	2018		900
201712	Dec	18	2018		500
201801	Jan	19	2018		400
201802	Feb	20	2018		900
201803	Mar	21	2018		600
201804	Apr	22	2018		800
201805	May	23	2018		700
201806	Jun	24	2018		600
201807	Jul	25	2019		300
201808	Aug	26	2019		400
201809	Sep	27	2019		400
201810	Oct	28	2019		500
201811	Nov	29	2019		100
201812	Dec	30	2019		700
201901	Jan	31	2019		500
201902	Feb	32	2019		200
201903	Mar	33	2019		200
201904	Apr	34	2019		200
201905	May	35	2019		600
201906	Jun	36	2019		200
201907	Jul	37	2020		900
201908	Aug	38	2020		900
201909	Sep	39	2020		500
201910	Oct	40	2020		300
201911	Nov	41	2020		800
201912	Dec	42	2020		800
202001	Jan	43	2020		500
202002	Feb	44	2020		900
202003	Mar	45	2020		100
202004	Apr	46	2020		800
202005	May	47	2020		100
202006	Jun	48	2020		600

[prat1507]

You can use YearMonth={'$(=date(Today(),'YYYYMM'))'}

in your set analysis.

Regards
Pratyush

[Anonymous]

Hi Pratyush

Thank you for your reply.  Will try it out and let you know.

Kind regards

Nayan

[Anonymous]

Hi Pratyush

I get the MTD value, but how do I get the Financial Year value?

Kind regards

Nayan

[prat1507]

You can use a similar expression as

FinancialYear={'$(=if(month(Today())>=4,Year(Today())+1,Year(Today())))'}

Regards

Pratyush

[Anonymous]

Hi Pratyush

Thank you for your reply.  Your formula works but only if it is today.

Kind regards

Nayan

[prat1507]

You can specify an date in place of today. You can get that from a field, since you wanted the result to be based on current date, I used today().

Let me know if you need something else.

Regards

Pratyush

[Anonymous]

ok, Thanks.

[Vaibhav_Pathak]

Hi Pratyush,

Can you explain how can we find data for a month before MTD. Like its jan 24 right now so in MTD we get jan 24 data, Now i want Dec 23 data.(MTD - 1).

Kindly help if you know.

Thanks,

Vaibhav

[Vaibhav_Pathak]

If anyone knows the solution kindly help me with this.