
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Fiscal Year Formula in Set Analysis
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?
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
For Jan, may be change it to this
If(Sum({$<[MONTH] = {"$(=If(Month(Today()) >= 10, Year(Today()) + 1, Year(Today())) & 01)"}>} [PLANNED_AMT]) = 0,
'-',
Sum({$<[MONTH] = {"$(=If(Month(Today()) >= 10, Year(Today()) + 1, Year(Today())) & 01)"}>} [PLANNED_AMT])
)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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])
)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Why do you need the additional 10?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am not entirely sure I understand what you are looking to do.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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])
)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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])
)

- « Previous Replies
-
- 1
- 2
- Next Replies »