topic Re: Rename Dimension x-axis values in QlikView

Rename Dimension x-axis values

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

Is there a way to label what values are shown on the x-axis?

For example:

I would like 45-2016 to be renamed 'Current Week', 44-2016, 'Last Week', 43-2016, 'Two Weeks Ago', and so on and so forth..

Is this possible?

Re: Rename Dimension x-axis values

neelamsaroha157 — Fri, 11 Nov 2016 20:22:29 GMT

Are you looking for something like this

If yes, then you can use this as your calculated dimension

Variables defined are

Hope this helps.

Re: Rename Dimension x-axis values

johnw — Fri, 11 Nov 2016 20:37:47 GMT

If current week is based on today and not on selections, you could load these labels in script as a [Week Label] field, say. Use that as your dimension instead of your Week field.

Re: Rename Dimension x-axis values

lucasdavis500 — Fri, 11 Nov 2016 21:04:30 GMT

If you're using Week year as your dimension, then MAX(RIGHT([WEEK_YEAR], 2)) is going to return '16' for 2016.... not the two digits for the actual week number. I think it may need to be LEFT

Re: Rename Dimension x-axis values

lucasdavis500 — Fri, 11 Nov 2016 21:08:38 GMT

My WEEK(DATE) returns all of my weeks to date, but has '53' included. This is causing an error with this formula. Any idea why '53' shows up in my data, even though my data begins 1/1/2016

Re: Rename Dimension x-axis values

lucasdavis500 — Fri, 11 Nov 2016 21:20:20 GMT

Hi John, I've tried this:

IF(Model2.WeekYear_Res = WEEK(WEEKSTART(today(1))) & '-' & WEEKYEAR(today(1)), 'Current Week', ) AS WeekRes_Flag

Which doesn't seem to be working properly. Is this what you were suggesting to do in the script? This is assigning 'Current Week' to every week.

using today(1) because apparently today(0) in script is the time of two script executions, not the latest script execution, according to:

http://qlikviewcookbook.com/2010/07/the-now-trap/

Re: Rename Dimension x-axis values

lucasdavis500 — Fri, 11 Nov 2016 22:00:20 GMT

Solved in script using

IF(Model2.Week_Res = WEEK(TODAY(0)) AND Model2.Year_Res = YEAR(TODAY(0)), 'Current Week', 0) AS WeekRes_Flag

Re: Rename Dimension x-axis values

lucasdavis500 — Fri, 11 Nov 2016 22:15:28 GMT

Correction: not resolved because qlikview doesn't like

IF(Model2.Week_Res = WEEK(TODAY(0))-1 AND Model2.Year_Res = YEAR(TODAY(0)), 'Current Week', 0) AS WeekRes_Flag

IF(Model2.Week_Res = (WEEK(TODAY(0))-1) AND Model2.Year_Res = YEAR(TODAY(0)), 'Current Week', 0) AS WeekRes_Flag

Does anyone know how to resolve Week # in the script?

EDIT: This doesn't work using TODAY(1), either.

Re: Rename Dimension x-axis values

johnw — Fri, 11 Nov 2016 22:43:08 GMT

I don't think I'd use flags. After some further thinking, I think I'd completely redefine my WeekYear field to be something like this. These should display as desired, but numerically are the first day of each week, so will also sort correctly if sorted numerically

,dual(if((weekstart(today())-weekstart(Date))/7<=5
,pick(1+ (weekstart(today())-weekstart(Date))/7
,'Current Week'
,'Last Week'
,'Two Weeks Ago'
,'Three Weeks Ago'
,'Four Weeks Ago'
,'Five Weeks Ago') // add as many as you want
,week(Date)&'-'&weekyear(Date)) // default if not named
,weekstart(Date)) as WeekYear

Re: Rename Dimension x-axis values

MarcoWedel — Fri, 11 Nov 2016 23:15:43 GMT

Hi,

another solution might be:

tabTempWeekNames:
LOAD Dual(WeekAge, WeekStart(Today(),1-RecNo())) as WeekAge
Inline [
WeekAge
Current Week
Last Week
Two Weeks Ago
Three Weeks Ago
Four Weeks Ago
Five Weeks Ago
];

tabShortCalendar:
LOAD *,
    WeekDay(Date) as WeekDay,
    WeekStart(Date) as WeekStart,
    WeekStart(Date) as WeekAge;
LOAD Date(Today()-RecNo()+1) as Date
AutoGenerate 35;

DROP Table tabTempWeekNames;

hope this helps

regards

Marco

Re: Rename Dimension x-axis values

lucasdavis500 — Mon, 14 Nov 2016 02:53:29 GMT

This is awesome, the more I think about it though, it may just be easier for the end-user to also have a list box, and then make their selection for the week in the year that they wish.

For example,

Week_Year Listbox:

Jan 1 - Jan 7 2016,

Jan 8 - Jan 15 2016,

......

Dec 25 - Dec 31 2016

Is this possible in the script without hardcoding each week in?

Re: Rename Dimension x-axis values

johnw — Mon, 14 Nov 2016 16:51:03 GMT

I'd probably simplify a little and just do this:

date(weekstart(Date),'MMM D YYYY') as Week_Start

But for exactly what you show, I suppose:

dual(date(weekstart(Date),'MMM D') & ' - ' & date(weekend(Date),'MMM D YYYY'),weekstart(Date)) as Week_Year

It would be a little funny, with weeks like Dec 27 - Jan 2 2016, but I suppose that's still clear enough without putting 2015 in there as well.

Re: Rename Dimension x-axis values

lucasdavis500 — Mon, 14 Nov 2016 16:59:46 GMT

I love it, I'm not sure why anyone would use "weekYear" without doing something like this, how is an end-user supposed to know what dates Week 42 of Year 2016 contained..... Having date ranges simplifies this

Re: Rename Dimension x-axis values

johnw — Mon, 14 Nov 2016 17:40:25 GMT

I'd say week numbers are for companies that run on week numbers, and care much less what the dates are. If what's important to you is that it's week 42, then it's reasonable to display it that way. But if what's important are the dates, then a range seems best, but also takes a lot of space. A starting or ending date can be a space-based compromise, particularly if people are used to seeing a single date stand for an entire week, as they are at our company.

Re: Rename Dimension x-axis values

lucasdavis500 — Mon, 14 Nov 2016 18:39:20 GMT

Very good point. I'll have to address this issue with my end-users and understand what they would like to see. Having the week is nice, but you are right that it takes up a lot of space.