Fiscal Year Formula in Set Analysis

[mskusace]

I have a set of KPIs for each Month in our Fiscal Year (OCT - SEP). The KPI is pulling from a field called "MONTH" which is 'yyyymm' and each KPI has it hard coded as '201910' for OCT Fiscal Year 2020, '201911' for NOV Fiscal Year 2020, etc.

I am trying to make the formula more dynamic to auto detect the fiscal year. I tried the formula below, but I get an error in the expression.

If Month >=10  THEN Fiscal Year = Current Year + 1 ELSE Fiscal Year = Current Year. Then concatenate the left 4 of the fiscal year with the hard coded MONTH value, which can remain static. 

IF(SUM({$<[MONTH]={IF(Month(Date(Today(),'M/D/YYYY h:mm:ss[.fff] TT'))>=10,RIGHT(Year(Date(Today()))+1,4),RIGHT(Year(Date(Today())),4))&'10'}>}[PLANNED_AMT])='0','-',SUM({$<[MONTH]={IF(Month(Date(Today(),'M/D/YYYY h:mm:ss[.fff] TT'))>=10,RIGHT(Year(Date(Today()))+1,4),RIGHT(Year(Date(Today())),4))&'10'}>}[PLANNED_AMT]))

How can I make this work or is there a better solution?

[sunny_talwar]

May be this

If(Sum({$<[MONTH] = {"$(=If(Month(Today()) >= 10, Year(Today()) + 1, Year(Today())) & 10)"}>} [PLANNED_AMT]) = 0,
'-',
Sum({$<[MONTH] = {"$(=If(Month(Today()) >= 10, Year(Today()) + 1, Year(Today())) & 10)"}>} [PLANNED_AMT])
)

[mskusace]

I added in "& 10", but I am not getting any results. I don't quite understand how that formula works, and why it's around double quotes. 

IF(SUM({$<[MONTH] = {"$(=If(Month(Today()) >= 10, Year(Today()) + 1 & 10, Year(Today())) & 10)"}>} [PLANNED_AMT]) = 0,'-',
SUM({$<[MONTH] = {"$(=If(Month(Today()) >= 10, Year(Today()) + 1, Year(Today())) & 10)"}>} [PLANNED_AMT]))

[sunny_talwar]

Why do you need the additional 10?

[mskusace]

I think I just actually told you bad logic in the post, that is my mistake. This might be easier than I was trying to make it.

I have the following in my data set.

201910	OCT	#
201911	NOV	#
201912	DEC	#
202001	JAN	#
202002	FEB	#
202003	MAR	#
202004	APR	#
202005	MAY	#
202006	JUN	#
202007	JUL	#
202008	AUG	#
202009	SEP	#

The KPIs are in a row from OCT - SEP. The KPIs are in order of "yyyy10", "yyyy11", "yyyy12", "yyyy01", etc. I want the "yyyy"to be dynamic so I don't have to keep modifying my app. So REALLY what I think I need is to just pull the year from the current year and then concatenate it with the static month "10", "11", etc. 

Apologies for the incorrect logic and question the first time! I appreciate your assistance!

[sunny_talwar]

I am not entirely sure I understand what you are looking to do.

[mskusace]

Okay, let's take 2019-2020 for example.

The KPI has "201910", "201911", 201912", "202001",etc. hard coded in the first formula I posted. These are the current formulas for the 12 KPIs.

IF(SUM({$<[MONTH]={'201910'}>}[PLANNED_AMT])='0','-',SUM({$<[MONTH]={'201910'}>}[PLANNED_AMT]))

IF(SUM({$<[MONTH]={'201911'}>}[PLANNED_AMT])='0','-',SUM({$<[MONTH]={'201911'}>}[PLANNED_AMT]))

IF(SUM({$<[MONTH]={'201912'}>}[PLANNED_AMT])='0','-',SUM({$<[MONTH]={'201912'}>}[PLANNED_AMT]))

IF(SUM({$<[MONTH]={'202001'}>}[PLANNED_AMT])='0','-',SUM({$<[MONTH]={'202001'}>}[PLANNED_AMT]))

ALL THE WAY TO

IF(SUM({$<[MONTH]={'202009'}>}[PLANNED_AMT])='0','-',SUM({$<[MONTH]={'202009'}>}[PLANNED_AMT]))

I want to change it so the '2019' and '2020' in the formulas automatically update, so I don't have to keep updating the app. 

If the CURRENT DATE is January 2020, I want the year for the KPI for October to be 2019 ('201910'). I was trying to use the CURRENT DATE so I wouldn't have to hard code in 2019 or 2020 in the metric. 

So I think for the OCT KPI, I would do, IF CURRENT MONTH >=10 THEN CURRENT YEAR ELSE CURRENT YEAR - 1. 

Then the JAN - SEP KPI would be, IF CURRENT MONTH < 10 THEN CURRENT YEAR ELSE CURRENT YEAR + 1.

I hope that clarifies it. Would that be a good approach?

[mskusace]

I need the output of the formula to be 'yyyymm'. For the OCT KPI, it will always be 'yyyy10' (Ex. Currently, it would be '201910'). For the NOV KPI, it will always be 'yyyy11' (Ex. Currently, it would be '201911'). I need the formula to get the YEAR and the month will be static.

[sunny_talwar]

So, try this

If(Sum({$<[MONTH] = {"$(=If(Month(Today()) >= 10, Year(Today()), Year(Today())+1) & 10)"}>} [PLANNED_AMT]) = 0,
'-',
Sum({$<[MONTH] = {"$(=If(Month(Today()) >= 10, Year(Today()), Year(Today())+1) & 10)"}>} [PLANNED_AMT])
)

This should be equivalent of this

If(Sum({$<[MONTH] = {"201910"}>} [PLANNED_AMT]) = 0,
'-',
Sum({$<[MONTH] = {"201910"}>} [PLANNED_AMT])
)

[mskusace]

It works for OCT - DEC, but it is not working for JAN-SEP.

For JAN, I used the following, but it is not working. In my data, it I see '202001', '202002', etc.

If(Sum({$<[MONTH] = {"$(=If(Month(Today()) >= 10, Year(Today()), Year(Today())+1) & 01)"}>} [PLANNED_AMT]) = 0,
'-',
Sum({$<[MONTH] = {"$(=If(Month(Today()) >= 10, Year(Today()), Year(Today())+1) & 01)"}>} [PLANNED_AMT])
)