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: 
CVR
Creator
Creator

Renaming Dimension Values on Straight Table

Hello QlikGeeks,

 

May I know if it is possible to rename the values of a Dimension used in a straight table? Example I have a field called Year and the values are 2021,2020,2019,........so on. I am using this field as a dimension in a straight table and only last 3 years of data is displayed from the selected date i.e.,  2021,2020,2019 and I want to display the names as Current12, Last12, Previous12 instead of 2021,2020,2019 respectively. 

 

Current:

CVR_0-1621020801320.png

Expected:

2019 as Previous12

2020 as Last12

2021 as Current12

 

Thanks in Advance,

CVR

@sunny_talwar,@marcus_sommer , @swuehl , @Gysbert_Wassenaar , @tresesco QlikView 

 

1 Solution

Accepted Solutions
marcus_sommer

In regard to my first example you could it apply in this way:

... $(=max(year([Order_Date]))-2) ...

or

... $(=year(max([Order_Date]))-2) ...

but it's much better to do this already within the script. No period-field should be created, formatted or in any way adjusted within the UI else everything should be done within a master-calendar to be able to use native fields in the UI.

- Marcus 

View solution in original post

6 Replies
OceanView
Contributor II
Contributor II

You could create a separate table that links to your primary table:

Load * INLINE [

  Year, Display_Year

  2019, Previous12

  2020, Last12

  2021, Current12

];

Then use Display_Year in the column.

If this is something that you want to update when the new year comes around, you can define variables for the 3 fields.  Then, in the above table you would use the value of the variables instead of the year.

CVR
Creator
Creator
Author

Thanks a lot for the quick reply . The Year field values changes according to the date selected on the main page i.e. if  2018 was selected then the year Column values are

2018 

2017 

2016 

So whatever may be the date selected the straight table Year column should only display 

Previous12

Last12

Current12

Thank you,

CVR

 

 

OceanView
Contributor II
Contributor II

I'm not sure what your selection page will look like and how it directly links to the next page.  

So, I will assume that the first page will only select the Year.  For this list, I'll call it Year_selector.

Create a variable in the load script:  set vValue=2021;  // <-- Could be automated to current year

load* inline [
Year_selector
2021
2020
2019
2018
2017
];

YearTable:
Load * INLINE [
Year, Display_Year
2019, Previous12
2020, Last12
2021, Current12
];

Your_Normal_Data:
Load Year, 
   ... 
from [file] (); ];

Then, go to Document Properties/Server and select Enable Dynamic Data Update

Then, go to Document Properties/Triggers and I created an OnSelect action for the Field Event Triggers.  Select Add Action(s)... then Add. Then, select External followed by Dynamic Update.

Create two actions:

1) Set Variable 

- Variable: vValue

- Value: =GetFieldSelections(Year_selector)

2) Dynamic Update

- Statement:

      Update YearTable SET Year=vValue WHERE Display_Year='Current12';
      Update YearTable SET Year=vValue-1 WHERE Display_Year='Last12';
      Update YearTable SET Year=vValue-2 WHERE Display_Year='Previous12';

OceanView_1-1621032619400.png

To get your data to display only the 3 years, on the Presentation tab of the chart/table, for the Display_Year Field select Omit Rows Where Field is NULL.

 

marcus_sommer

You could try it with a calculated dimension like this one:

pick(match([Year], $(=max([Year])-2), $(=max([Year])-1), $(=max([Year])),
'previous year, 'last year', 'current year') 

whereby like hinted from the return values it applies to years and not to rolling 12 month which you didn't mention but hint's it in the return values. For this you couldn't use a year-information else you need to access the data on a year-month period-level - in sake of simplicity here created with: (year * 12 + month) as ContinuousPeriod and then using an adjusted calculated dimension like:

pick(match(-1, $(=max(ContinuousPeriod)) - ContinuousPeriod  <= 12,
                               $(=max(ContinuousPeriod)) - ContinuousPeriod  <= 24,
                               $(=max(ContinuousPeriod)) - ContinuousPeriod  <= 36),
'current12', 'last12', 'previous12')

- Marcus

CVR
Creator
Creator
Author

Thank you for the quick reply.  Actually my dimension field Year is derived from other field like this

"=Year(Order_Date)"  and Order_Date field format is 'YYYY-MM-DD'

marcus_sommer

In regard to my first example you could it apply in this way:

... $(=max(year([Order_Date]))-2) ...

or

... $(=year(max([Order_Date]))-2) ...

but it's much better to do this already within the script. No period-field should be created, formatted or in any way adjusted within the UI else everything should be done within a master-calendar to be able to use native fields in the UI.

- Marcus