Max() Function within Set Analysis?

[glenn2460]

The following has the anticipated result, however I would like to replace the hard-coded Fiscal Year and Period using the Max() function so as to display the result for the 'max' (current) year and 'max -1' (previous) period.  There are 13 periods per Fiscal, so Max() needs to select the maximum to date for the current year instead of always selecting 12.

Sum({$<[Fiscal Year] = {"2020/2021"}, [Fiscal Period] = {7}>} [Total Loss])

Any suggestions on how to integrate the Max() function (or alternative solutions) would be most appreciated.

Thank you.

Glenn

[sunny_talwar]

@glenn2460 How about using MaxString?

Sum({$<[Fiscal Year] = {"$(=MaxString([Fiscal Year]))"}, [Fiscal Period] = {7}>} [Total Loss])

[glenn2460]

Thank you Sunny, using MaxString correctly evaluates the Fiscal Year as the current year.

When I applied the same concept to the Fiscal Period, instead of evaluating it as 7, it was calculated as 12.  I understand that as there are 13 periods, however since we're currently in Period 8 would you know a way that I might be able to alter the expression?  The end result is to have the KPI show the loss for the previous period of this fiscal.   Each year DOES have 13 periods, however for this purpose I'm really only interested in the 'periods to date' so I suspect a further check will be required.

Thank you very much for your assistance.

Glenn

[sunny_talwar]

@glenn2460 I am not sure I understand, would you be able to share the values that you have and the value that you want to pick?

[glenn2460]

I have a KPI in which I wish to display the Sum([Total Loss]) for the current Fiscal Year (your suggestion of MaxString evaluates correctly as '2020/2021') and _previous_ [Fiscal Period].

There are 13 fiscal periods in a fiscal year and they are simply numbered 1-13 in the applicable table.

What I'm attempting to do is select the fiscal period _prior_ to the current fiscal period.  The last completed fiscal period. Thinking now, perhaps Max() is the wrong direction as Max() appears to correctly evaluate to 13 -- and therefore Max([Fiscal Period) -1 will always evaluate to 12. 

Today we are in [Fiscal Period] 8, so I need the expression to evaluate to 7.  My thought was that if the year is selected then so far there are only records up to an including Period 8 so that would be the Max() period.  I can see where that's not working as intended and that a intermediate step or other check will be required.

Thanks again Sunny, appreciate your time.

Take care.

Glenn

[dwforest]

Use  a master calendar that contains Fiscal Period as a dimension, then:

Sum({$<[Fiscal Year] = {"$(=MaxString([Fiscal Year]))"}, [Fiscal Period] = {"$(=Max({<Date={Today()}>}[Fiscal Period]))"}>} [Total Loss])

[glenn2460]

Thanks for the reply  @dwforest 

Just doing some reading on the subject now as they're not something I've used before.

Thanks again.

Glenn

[glenn2460]

I've incorporated the Master Calendar script found here (https://community.qlik.com/t5/Qlik-Sense-Documents/Understanding-the-Master-Calendar-video/ta-p/1479...) and it appears to be functional.  Thanks again for the suggestion @dwforest.  

It has given me access to Quarters, Weeks, Years, etc. however I need to also accommodate a April-March Fiscal Year as well as thirteen four week Fiscal Periods commencing April 1st.

Any info or links to material explaining ways to add this extra data would be most appreciated.

Thanks.

Glenn