topic Re: Qliksense:develop a single calculated date dimension showing - Year, Quarter, Month, Week, Day v in QlikView

Qliksense:develop a single calculated date dimension showing - Year, Quarter, Month, Week, Day view

rahulgoyal1287 — Sat, 16 Nov 2024 05:12:35 GMT

Hi Community Members,

I need some help in designing a calculated dimension which should work in following manner -

When a year is finished then dates of that year should be clubbed under a year, to be named as 'Year - <year>'

Then, when a quarter is finished then dates of that quarter should be mapped under a quarter , named as 'Q <Quarter Number> - <Year> '

in the same way then Month, to be named as '<Month> - <Year>'

then Week, to be named as '<Week> - <Year>'

rest the remaining days which are not yet mapped under any week.

This is required in ascending order.

I have already developed a script which is working fine partially with only a problem of values coming in descending order... mentioned below -

IF(YEAR(CLOSED)<> YEAR(Today()),'Yr-'& YEAR(CLOSED),

IF(QuarterName(CLOSED)<> QuarterName(Today()),'Q' & Ceil(NUm(Month([CLOSED]))/3)&'-'&year([CLOSED]),

IF(Month(CLOSED)<> Month(Today()),'M-'& Month([CLOSED])&'-'&year([CLOSED]),

IF(Week(CLOSED)<> Week(Today()), Week([CLOSED])&'-'&year([CLOSED]),dayname(CLOSED))))) AS CALC_FIELD

Screenshot for your reference:

Request you to please help me in aligning the script to achieve the desired results. Many Thanks in advance.

Best Regards,

Rahul Goyal

jobsonkjoseph — Mon, 19 Nov 2018 07:36:39 GMT

Hi, Have you tried using order by clause.

rahulgoyal1287 — Wed, 21 Nov 2018 04:37:12 GMT

Hello J,

Yes, I have used sorting feature in descending order to get the following sequence -

Year > Quarter > Month > Week > Days

It is working partially by adhering to above sequence however within any specific bucket 'Lets say Quarter' it is coming in descending order.

Thanks Rahul

rahulgoyal1287 — Fri, 25 Jan 2019 05:17:15 GMT

This has been resolved by using a solution where we enhance our existing algorithm by prefixing some extra spaces in the values , like -

4 spaces before Yr

3 spaces before Q

2 spaces before M

1 space before Week

& it will sort well alphabetically......

IF(YEAR(CLOSED)<> YEAR(Today()),'Yr-'& YEAR(CLOSED),

IF(QuarterName(CLOSED)<> QuarterName(Today()),'Q' & Ceil(NUm(Month([CLOSED]))/3)&'-'&year([CLOSED]),

IF(Month(CLOSED)<> Month(Today()),'M-'& Month([CLOSED])&'-'&year([CLOSED]),

IF(Week(CLOSED)<> Week(Today()), Week([CLOSED])&'-'&year([CLOSED]),dayname(CLOSED))))) AS CALC_FIELD

Hope this helps.