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

[rahulgoyal1287]

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]

Hi, Have you tried using order by clause.

[rahulgoyal1287]

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 · Accepted solution]

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.