Variable set using Field Values

[Anonymous]

Hello!

I am needing some assistance. I currently have a Fiscal Year Calendar that was used as a Binary for my current application. Within my application, I am wanting to do a YOY comparison for the past 3 years, but only up to today's date in all three years.

Currently, I have the fields

*[Day Number] which is representative of the Fiscal Year Day Number

*Date (MM/DD/YYY)

*FYDay which is in the format of YYYY & Day Number.

With the FYDay being in this format, we will be able to do set analysis with today's max fyday and simply subtract 1000 for last year and 2000 for two years ago, etc.

I have set a variable vMaxDate = Today(0). Now I am stuck with trying to set a variable for vMaxFYDay. Given that the FYDay field is related to the Date field, I thought I should be able to set my vMaxFYDay using the vMaxDate.

I have quite a bit of experience in writing VBA for excel: I would think I am needing an equivalent for INDEX&MATCH. I have attempted using multiple functions within Qlik, but none of them are giving me what I'm needing.

I would truly appreciate any help that I can get in trying to get this figured out! Thanks!

[olivierrobin]

hello

did tou try date() date#() functions which formats dates in various ways ?

[tresesco]

Assuming you have a date field FYDay (actually text) in YYYYD format, to get max out of it, you could try like:

=Date(Max(Date#(FYDay, 'YYYYD')), 'YYYYD')

[petter]

You should be able to find the max FYDay by doing something like this:

     Only( {<Date={'$(=Date(Today(0),''MM/DD/YYY''))'}>} FYDay)

Notice that '' is two consecutive single quotes ' and not a single double quote " - which looks exactly the same...

Only() picks out the only value that is returned from the set expression. If there is more than one value the Only() function will return null.

And looking up a particular regular date should associate with a single fiscal year day. A one to one correspondence.

The associate engine of Qlik will be able to pick the FYDay after a filtering has been done down to a single date....