Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Expected:
2019 as Previous12
2020 as Last12
2021 as Current12
Thanks in Advance,
CVR
@sunny_talwar,@marcus_sommer , @swuehl , @Gysbert_Wassenaar , @tresesco QlikView
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
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.
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
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';
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.
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
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'
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