CY and LY flags - Need help

[dushyant]

Hello,

Im learning to create flags in my master calendar. I know how to get ytd mtd LY CY data using set analysis in the front end but I would like to learn how to do it in the scripting part using flags. I have used the if statement to create CY and LY flags but somehow its showing me an error. I have searched the community and found some post where they have used inyear2date or inyear or year2date functions. which Im unable to understand its working. I have attached the screenshot of the script i have used and the error which is coming when I load it. please help...

ErrorMaster Calendar Script

[rubenmarin · Accepted solution]

Hi, to use max you need to use a group by, it this case it would be better if you calculate the max outside and store it in a variable to use in your calendar.

// After TempCal get the max year
MaxDate:
LOAD Max(X) as MaxDate Resident TempCal;
LET vMaxYear=Year(Peek('MaxDate',0,'MaxDate'));
DROP Table MaxDate;

// and use it in calendar
LOAD
  ...,
  If(Year(X)=$(vMaxYear),1,0) as CY_flag,
  If(Year(X)=$(vMaxYear)-1,1,0) as LY_flag

[dushyant]

Thank you Ruben for the solution. It's working now.

I have one silly doubt. I want to know  why Peek function is required while storing the max date or min date in the variable. I am unable to understand what's happening behind when we use the peek function. I tried to understand it using an example with mock data I created. But when we store it in a variable using peek function, I don't understand it.

I see that we use these variables in our master calendar, with the functions like iterno. and autogenerate when creating the TempCal table. I know we do this to find all the dates between the mindate and the maxdate, But I am inquisitive to know the logic behind it how it happens. 

  I have shared the screenshots of my example I made.

 Peek function used in the same table - ScriptPeek function used in the same table - OutputPeek Function used with Resident Load - ScriptPeek Function used with Resident Load - Output

As you can see that when I apply peek in the same table with the data then there is null in some rows. But if I use resident and then apply peek function then the rows are filled with the values.

Please if you help me clear my doubt.

[rubenmarin]

Hi, Peek() is used to retrieve data loaded in a table: https://help.qlik.com/es-ES/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecor...

When you use it using -1 or -2 iy get the last or the 2nd last loadedrecord of the table. If this table it's still loading data this last record will change, while it's loading the first record there is still no record loaded so it returns null, the next will return the frist records loaded.

Doing the same sith a resient table the number of records doesn't changes so it always returns the last and 2nd last records loaded.