Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
LeHaoNguyen
Contributor III
Contributor III

Calculated Dimension with Colors

Hi all,

i want to display a complicated dimension in qlik sense the dimension works like it is

i got a table with 6 Dates CheckInDT,ClassDT,PREPDT,SCANDT,CheckOutDT and PROCDT

and i want to split the dimension like following: in our process they go step by step from CheckInDT to PROCDT in the above order. I want to split the dimensions in how many days it is in the last processstate:

=IF(IF(PROCDT <> NULL, NULL,IF(CheckOutDT <> NULL,FLOOR(today(1))-FLOOR([CheckOutDT.autoCalendar.Date]),
IF(SCANDT <> NULL, FLOOR(today(1))-FLOOR([SCANDT.autoCalendar.Date]),
IF(PREPDT <> NULL,FLOOR(today(1))-FLOOR([PREPDT.autoCalendar.Date]),
IF(ClassDT <> NULL,FLOOR(today(1))-FLOOR([ClassDT.autoCalendar.Date]),
IF(CheckInDT <> NULL,FLOOR(today(1))-FLOOR([CheckInDT.autoCalendar.Date]))))))) >= 100,dual('>= 100 Tage', 3),
IF(IF(PROCDT <> NULL, NULL,IF(CheckOutDT <> NULL,FLOOR(today(1))-FLOOR([CheckOutDT.autoCalendar.Date]),
IF(SCANDT <> NULL, FLOOR(today(1))-FLOOR([SCANDT.autoCalendar.Date]),
IF(PREPDT <> NULL,FLOOR(today(1))-FLOOR([PREPDT.autoCalendar.Date]),IF(ClassDT <> NULL,FLOOR(today(1))-FLOOR([ClassDT.autoCalendar.Date]),
IF(CheckInDT <> NULL,FLOOR(today(1))-FLOOR([CheckInDT.autoCalendar.Date]))))))) <= 30,dual('<= 30 Tage', 1),
IF(IF(PROCDT <> NULL, NULL,IF(CheckOutDT <> NULL,FLOOR(today(1))-FLOOR([CheckOutDT.autoCalendar.Date]),
IF(SCANDT <> NULL, FLOOR(today(1))-FLOOR([SCANDT.autoCalendar.Date]),
IF(PREPDT <> NULL,FLOOR(today(1))-FLOOR([PREPDT.autoCalendar.Date]),
IF(ClassDT <> NULL,FLOOR(today(1))-FLOOR([ClassDT.autoCalendar.Date]),
IF(CheckInDT <> NULL,FLOOR(today(1))-FLOOR([CheckInDT.autoCalendar.Date]))))))) >= 31 AND IF(PROCDT <> NULL, NULL,IF(CheckOutDT <> NULL,FLOOR(today(1))-FLOOR([CheckOutDT.autoCalendar.Date]),
IF(SCANDT <> NULL, FLOOR(today(1))-FLOOR([SCANDT.autoCalendar.Date]),
IF(PREPDT <> NULL,FLOOR(today(1))-FLOOR([PREPDT.autoCalendar.Date]),
IF(ClassDT <> NULL,FLOOR(today(1))-FLOOR([ClassDT.autoCalendar.Date]),
IF(CheckInDT <> NULL,FLOOR(today(1))-FLOOR([CheckInDT.autoCalendar.Date]))))))) <=99, dual('>30 & <100', 2),NULL)))

so i got a calculated Dimension with under 30days between 31 and 99 days and over 100 days this seems to work like this first question is is there an easier way to achieve this because this seems like a preeeeeeettty long  chain of IF statements.

The above statement works for the display of the dimension but i also want to set the colors of theses dimension in the bar chart.

When i go to Colors in the bar chart and set color by expression and input the same statement just replace the dual with the colorcodes:

=IF(IF(PROCDT <> NULL, NULL,IF(CheckOutDT <> NULL,FLOOR(today(1))-FLOOR([CheckOutDT.autoCalendar.Date]),
IF(SCANDT <> NULL, FLOOR(today(1))-FLOOR([SCANDT.autoCalendar.Date]),
IF(PREPDT <> NULL,FLOOR(today(1))-FLOOR([PREPDT.autoCalendar.Date]),
IF(ClassDT <> NULL,FLOOR(today(1))-FLOOR([ClassDT.autoCalendar.Date]),
IF(CheckInDT <> NULL,FLOOR(today(1))-FLOOR([CheckInDT.autoCalendar.Date]))))))) >= 100,red(),
IF(IF(PROCDT <> NULL, NULL,IF(CheckOutDT <> NULL,FLOOR(today(1))-FLOOR([CheckOutDT.autoCalendar.Date]),
IF(SCANDT <> NULL, FLOOR(today(1))-FLOOR([SCANDT.autoCalendar.Date]),
IF(PREPDT <> NULL,FLOOR(today(1))-FLOOR([PREPDT.autoCalendar.Date]),IF(ClassDT <> NULL,FLOOR(today(1))-FLOOR([ClassDT.autoCalendar.Date]),
IF(CheckInDT <> NULL,FLOOR(today(1))-FLOOR([CheckInDT.autoCalendar.Date]))))))) <= 30,green(),
IF(IF(PROCDT <> NULL, NULL,IF(CheckOutDT <> NULL,FLOOR(today(1))-FLOOR([CheckOutDT.autoCalendar.Date]),
IF(SCANDT <> NULL, FLOOR(today(1))-FLOOR([SCANDT.autoCalendar.Date]),
IF(PREPDT <> NULL,FLOOR(today(1))-FLOOR([PREPDT.autoCalendar.Date]),
IF(ClassDT <> NULL,FLOOR(today(1))-FLOOR([ClassDT.autoCalendar.Date]),
IF(CheckInDT <> NULL,FLOOR(today(1))-FLOOR([CheckInDT.autoCalendar.Date]))))))) >= 31 AND IF(PROCDT <> NULL, NULL,IF(CheckOutDT <> NULL,FLOOR(today(1))-FLOOR([CheckOutDT.autoCalendar.Date]),
IF(SCANDT <> NULL, FLOOR(today(1))-FLOOR([SCANDT.autoCalendar.Date]),
IF(PREPDT <> NULL,FLOOR(today(1))-FLOOR([PREPDT.autoCalendar.Date]),
IF(ClassDT <> NULL,FLOOR(today(1))-FLOOR([ClassDT.autoCalendar.Date]),
IF(CheckInDT <> NULL,FLOOR(today(1))-FLOOR([CheckInDT.autoCalendar.Date]))))))) <=99, yellow(),NULL)))

 

it shows me just all in grey instead of the desired colors green red and yellow can you explain why its all grey and how to get the bars in the right colors?

 

Greetings

Le-Hao

 

Labels (2)
0 Replies