Julian Calendar selections

[Hannes]

Hi all.

I am working with a Julian Calendar which is built via a 3rd party using SQL. I use that same SQL script in Qlik Sense to store it as a QVD with only the current and previous calendar year.

I have a date field that contains the dates for the current and previous year and then also a DayNum field to whichs numbers the days of the year.

To start off with an example

My Client wants to be able to select a date in the latest financial year only and that should link up with the corresponding date based on the day number in order to do a CYTD vs PYTD calculations.

Just to add an example. Once we get to Fin Year 2027 and 2028, my year will start with 2026/06/29 for fin 2027 and the fin year 2028 will start on 2028/07/05 

Would this be something that I do in the front end or the back end and also, how would I go about it? 

[marcus_sommer]

All calendar-related information should be created within the script and the day-number could be easily derived against the year and interrecord-function like:

if(Year = previous(Year), peek('DayNumber') + 1, 1) as DayNumber

and an UI expression may relate to it with:

sum({< Date, DayNumber = p(DayNumber)>} Value)

[Hannes]

This is the script I'm running after the QVD is created from the SQL script

[marcus_sommer]

FIN_YearDesc seems to be suitable for the above mentioned measurement. Important is by the use of interrecord-functions to ensure the needed ordering of the records - which usually exists by the common calendar-creation within Qlik.

By loading the data from an external source you may need an extra pre-loading of the data to be able to apply an appropriate order by statement within a resident-load.

Beside this - if there is already a DayNum logic based on an offset-calculation you may have already everything what you wanted and could skip the above stuff.  

[TauseefKhan]

1- Load the Julian Calendar QVD:

2- Calculate the Financial Year Start Date:

Create a field that determines the start date of the financial year based on your criteria (e.g., 2026/06/29 for 2027).

JulianCalendar:
LOAD *,
IF(DateField >= vFinYearStart AND DateField <= vFinYearEnd, 1, 0) as IsLatestFinYear
RESIDENT JulianCalendar;

1- Order the Data:
• Ensure the data is ordered correctly using an ORDER BY clause if necessary.

Frontend:

1- To calculate sums for the current and previous financial years dynamically.

vCYTD = Sum({<Year = {$(=Max(Year))}, IsLatestFinYear = {1}>} Sales)
vPYTD = Sum({<Year = {$(=Max(Year)-1)}, IsLatestFinYear = {1}>} Sales)

2- Use the variables to display the CYTD vs PYTD calculations.

[Hannes · Accepted solution]

Thank you all for your time and effort regarding my querry. I managed to resolve the issue that I was facing with the following scenario. 

So, I load my Julian calendar qvd as per above. Then because I only load 2 financial years of data, I use the peek function to set variables

Once that is done, I then split the calendar into 2 with the min and max financial year. and use the DayNum field that I created as my key.

With this, I am able to use the max year table's calendar date in my filter to select my date/date range and that will then select my corresponding dates in the previous year based on the DayNum field

In the front end, I then make use of set analysis for my CYTD & PYTD calculations. Works perfect and compared the results with what the client has.

Thank you all once again for your time and effort