topic Re: Color on a bar chart based on value (value = date from master calendar) in QlikView

Color on a bar chart based on value (value = date from master calendar)

lessassy — Wed, 25 Nov 2020 16:16:04 GMT

Hello Everyone,

I'm using the master calendar in my script.

And I used that to display specific value based on weekyear (weeknumber and year)

What i want to do is to put in Green the most recent bar in the chart. The most recent one is 20.2018 and i want it to be in different color for comparison aspect.

I know i have to go to background in the expression view

But as i'm a newcomer for qlikview i don't know the right formula to put in order to have the bar chart in blue for the latest (most recent week).

As I previously said I have a master calendar in my script

MasterCalendar:

Load

TempDate as Created,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

And the field of the bar chart (which represent the expression i used in the form (sum(LossBU)) is

Anyone can help ?

Thank You

Re: Color on a bar chart based on value (value = date from master calendar)

m_woolf — Fri, 18 May 2018 17:34:28 GMT

if(WeekYear = max(WeekYear),lightgreen())

Re: Color on a bar chart based on value (value = date from master calendar)

lessassy — Wed, 23 May 2018 09:53:50 GMT

It doesn't work unfortunately.

DOes it have to do with the fact that almost every bar chart of mine have a empty space like in the image below

ANd on the other hand, it (kind of) work for another chart. Except that it doesn' take into account the last month

Does this struggle have to do with the master calendar ?

Thank you in advance

Re: Color on a bar chart based on value (value = date from master calendar)

Anonymous — Thu, 24 May 2018 10:34:10 GMT

Can you post a sample version of the app?

Re: Color on a bar chart based on value (value = date from master calendar)

shansundar — Thu, 24 May 2018 10:44:26 GMT

Try using Maxstring like below,

if(only({1}WeekYear) = maxstring(WeekYear),lightgreen())

I just included {1} to ignore all report selections.

Thanks,

Shan S

Re: Color on a bar chart based on value (value = date from master calendar)

Anil_Babu_Samineni — Thu, 24 May 2018 10:52:30 GMT

Once you done that which provide by mwoolf you need to do "Suppress When Value is Null" for that dimension

Re: Color on a bar chart based on value (value = date from master calendar)

lessassy — Thu, 24 May 2018 15:51:28 GMT

THank you for trying but unfortunately it's not working

Everything turn green whereas it's not really what i want.

Here is my master calendar, just in case it can help:

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(Created) as minDate,

max(Created) as maxDate

Resident MyTable1;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate as Created,

week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar; "

Thank you for your help

Re: Color on a bar chart based on value (value = date from master calendar)

lessassy — Thu, 24 May 2018 15:52:55 GMT

I cannot do suppres when value is null because i also want to display the month where there are no datas.

But THis is not that important.

What i can't figure out is why the formula doesn't work.

Does it have to do with the master calendar ?

Re: Color on a bar chart based on value (value = date from master calendar)

Anil_Babu_Samineni — Fri, 25 May 2018 03:25:55 GMT

Can you attach application?

Re: Color on a bar chart based on value (value = date from master calendar)

lessassy — Fri, 25 May 2018 07:42:02 GMT

Here are the files i use.

I have qlikview personnal edition by the way. SO I won't be able to use the file once modify...

Re: Color on a bar chart based on value (value = date from master calendar)

Janneke — Fri, 25 May 2018 13:04:31 GMT

Hi Yann,

I have looked at your qvw and encountered the issue that your WeekYear field is a string. So using Max() will not work. Instead, use MaxString.

However, since you have put the weeks in front of the year, week 9-2018 will be the max (since 9-.... is larger than 20-....).

Maybe you can fix this first by changing the calender putting year in front of week and then try the solutions mentioned above.

Greetings, Janneke.

Re: Color on a bar chart based on value (value = date from master calendar)

lessassy — Fri, 25 May 2018 13:20:12 GMT

Here is my modified master calendar:

MasterCalendar:

Load

TempDate as Created,

Year(TempDate) As Year,

Month(TempDate) As Month,

week(TempDate) As Week,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Here is a screenshot of the modification

So If you are using a master calendar, could i have a sample where i could use min and max. I mean it's because of that that it's not working, right ?

Re: Color on a bar chart based on value (value = date from master calendar)

Janneke — Fri, 25 May 2018 13:28:35 GMT

Hi Yann,

Try WeekYear(TempDate) & '-' & Num(Week(weekstart(TempDate)),'00') as WeekYear.

This will put the year in front and puts a zero in front of one-digit-weeks (9 --> 09). Sorting is then correct.

If you are not sure or it doesn't work, try testing parts of the expression (i.e. =MaxString(WeekYear)) in a text box, so you can understand how the definition is built.

Janneke.

Re: Color on a bar chart based on value (value = date from master calendar)

lessassy — Fri, 25 May 2018 13:28:42 GMT

Besides I have a cycle group in this chart (Weeks and Month)

What would be the exact formula to include month as well ?

Re: Color on a bar chart based on value (value = date from master calendar)

lessassy — Fri, 25 May 2018 13:46:41 GMT

It's working on a tex box but not on a bar chart.

It drives me crazy.

But so far progress was made and i thank you for that. Do you think it has to do with the bar chart property ?

Besides i wanted to ask you

I have a cycle group in this chart (Weeks and Month)

What would be the exact formula to include month as well ?

Thank you thank you

Re: Color on a bar chart based on value (value = date from master calendar)

lessassy — Fri, 25 May 2018 13:55:46 GMT

It work when i Specify the weekyear...

Re: Color on a bar chart based on value (value = date from master calendar)

Janneke — Fri, 25 May 2018 14:10:35 GMT

Hi Yann,

Now you can put WeekYear in a variable:

vMaxWeekYear =MaxString(WeekYear)

Use this in the expression:

{<WeekYear={'$(=vMaxWeekYear)'}>}

Does this work?

Janneke

Re: Color on a bar chart based on value (value = date from master calendar)

lessassy — Fri, 25 May 2018 14:30:45 GMT

Sorry but i'm not very familiar for variable.

I added this in the script

But It doesn't appear neither in variable overview

nor in a set analysis within a text box...

Re: Color on a bar chart based on value (value = date from master calendar)

lessassy — Fri, 25 May 2018 14:43:12 GMT

I suceeded putting up the variable in the exact menus

But the text box doesn't recognize the variable as well 😕

Re: Color on a bar chart based on value (value = date from master calendar)

Janneke — Fri, 25 May 2018 15:06:49 GMT

You need to use dollar sign: $(vMaxWeekYear).

If this is not working, then try to put =-sign in the variable: =MaxString(WeekYear)