Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Simon_W
Contributor
Contributor

5-4-4 Financial Calendar with months overlapping calendar months

Hello!

We have a 5-4-4 Financial calendar that can span calendar years and months. This is the schedule for 2020:

FY = 29-Dec-2019 to 26-Dec-2020

M1 =  29-Dec-2019 to 01-Feb-2020

M2 = 02-Feb-2020 to 29-Feb-2020

M3 = 01-Mar-2020 to 28-Mar-2020

M4 = 29-Mar-2020 to 02-May-2020 

etc etc. Note the weeks go in 5-4-4 blocks and 35-28-28 day blocks.

 

I need to be able to compare my data for complete weeks only, so for example "Year to date to the end of last week", to "last year to date to the end of the same date the previous year" (offset of course).

LIkewise, the same with Months and Quarters.

Its getting very tricky and beyond my scope of knowledge - does anyone know if this is possible please, and if so how?

TIA!

Labels (2)
2 Solutions

Accepted Solutions
AndyC
Contributor II
Contributor II

The simplest solution I can think of is to generate a separate calendar and then map you transaction dates to it.

Since it is a data set that doesn't change - You can create the calendar in Excel

 

AndyC_0-1588862419178.png

 

View solution in original post

AndyC
Contributor II
Contributor II

Hmm, there are a few ways you could do this.

The simplest that I use is to create a year and period list box (if you set the year box to only allow a single selection it works best)

Then use a set analysis modifier along these lines...

Sum({<Year={$(=(Num(GetFieldSelections(Year)))-1)}>} Revenue)

This will take the current selections and filter them by a different year.

E.g.

AndyC_0-1589183399041.png

 

View solution in original post

6 Replies
AndyC
Contributor II
Contributor II

The simplest solution I can think of is to generate a separate calendar and then map you transaction dates to it.

Since it is a data set that doesn't change - You can create the calendar in Excel

 

AndyC_0-1588862419178.png

 

Simon_W
Contributor
Contributor
Author

Thanks Andy, would you mind giving me an example of how i'd use this in a set analysis to limit the data, e.g. trying to get sales for "current month to date", vs same period the previous year?

 

TIA.

AndyC
Contributor II
Contributor II

Hmm, there are a few ways you could do this.

The simplest that I use is to create a year and period list box (if you set the year box to only allow a single selection it works best)

Then use a set analysis modifier along these lines...

Sum({<Year={$(=(Num(GetFieldSelections(Year)))-1)}>} Revenue)

This will take the current selections and filter them by a different year.

E.g.

AndyC_0-1589183399041.png

 

Simon_W
Contributor
Contributor
Author

Thank you Andy, that makes a lot of sense. I'm still learning with these expresssions!

I'll give this a try today!

Simon_W
Contributor
Contributor
Author

This was exactly what I needed - thanks for your help!

Brett_Bleess
Employee
Employee

Following Design Blog post may be of some help too for future visitors:

https://community.qlik.com/t5/Qlik-Design-Blog/Recipe-for-a-4-4-5-Calendar/ba-p/1464069

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.