Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to have a line chart in Qlik Sense showing me the data for all the weeknumbers within 2 years from the current weeknummer.
I have been trying to figure out how to set this in the Qlik dashboard chart using a set analysis expression, but I can't figure out how to do this.
I have read other topics about this issue and tried the adjust the given solutions, but I did not succeed.
My situation is this: I have a date table in which the weeknumbers for a long time are loaded (also in the future week 01 2025 is already in the date table for example:
I want to determine what the year-weeknumber is for the most recent registration in the data so not all the future weeknumbers in the date table are also used. The registration used dca_datum as field for the startdate of the activity. I created a KPI to find the current max value of DCA_date and Qlik reports it is 31-12-2024. So DCA_Date cannot be used I assume.
so when the year-weeknumber for the most recent registration is 202414, I want the set analysis to use all the records in the time periode 202215 to 202414, so 2 full years.
How can I do this?
[edit]
I found a different variable 'DCE_datum' which is used to register the end date of the checks
This one is only loaded for actual performed checks.
So I could use DCE_datum to set the range for max 2 years.
And tried using this as limit for the chart dimension using this expression and choosing 'relative' and 'larger or equal to':
num(Year(max(DCE_Datum))-2 & (right(Max(DCE_IsoJaarWeek),2)+1))
(the result for this expression as shown in the kpi chart is 202215).
However, this doesnt work of course. I need something like this for a set analysis.
I am starting to believe that I do not have the rights to create set expressions. Perhaps it's caused by the app I'm using. It's an app that is maintained but a different department of the organisation which I have duplicated to my own stream to be able to create and edit myself.
Sum( {<[DCE_IsoJaarWeek]={"*"}>} [# Controles] ) << expression error
Sum( {<[DCE_IsoJaarWeek]={'202215'}>} [# Controles] ) << expression error
I gues no matter what I enter for this expression, I get an expression error.
I have never heard of that but maybe try a set analysis using a different field or in a different app. Very strange. It could be a data type issue with DCE_IsoJaarWeek and given that you can't access the load script to floor() it I'm guessing this course of inquiry is still unaddressed. If the set statement works on another field , like any field IE: SUM( {<City={'New York'}>} [# Controles]) ..where City is a data field and 'New York' is a value in the same data field... then its likely something with the field itself specifically in your app.
concering the data load script: all I can see is a qvd that is being loaded:
Load * From $(vQVDPath)DATUM_CONTROLE_EINDE.qvd (qvd);
I can open the QVD and the see field, but I cannot see how the DCE_IsoJaarWeek is being loaded into this table.
This one also returns an set analysis error:
sum({<PRO_Centrumbrigade = {"*Noord*"}>} [# Controles])
Looks like I cannot create any set analysis expression.
Perhaps there's something gone wrong in the duplicated version of this app in my stream, because also setting a limit to the axis doesn't work at all:
The set expression is now correct:
sum({<DCE_Iso_JaarWeek={">=$(vMinWeek)<=$(vTodayWeek)"}>} [#CON_Controles])
turns out I used # Controles as measure where I had to use the original name '#Con_Controles' ##
(I used the label/master item name).
I probably also used it at the wrong place: I used it to limit the dimension but a sum has to be used for a measure isn't it? I moved the set expression to the measures of this chart and with the corrected expression it is now correct, although, still not limiting the x-axis for the DCE_IsoJaarWeek values. Limiting the x-axis is another challenge. Much to learn still...
The limit I set for the dimension seems to limit the measure. that is odd right?
I will close the app and start again.
About the limiting the x-axis for DCE_IsoJaarWeek:
I got feedback I just needed to enter 202215 to test the limit, but in my case, it still did not work.
Probably I am still doing something wrong, but I have not found the mistake yet.
When I now enter this expression for the dimenion:
=Sum({<DCE_IsoJaarWeek={">=$(vMinWeek)<=$(vTodayWeek)"}>} [#CON_Controles])
the expression is correct but I get an 'invalid dimension' error.
what is the correct place for this expression? Dimension of measure?
edit:
Ok, so I finally got it working correctly:
I have entered the set analysis expression in the measure.
=Sum({1<DCE_IsoJaarWeek={">=$(vMinWeek)<=$(vTodayWeek)"}, PRO_Domein={"MTV*"}>} [#CON_Controles])
Just to keep this selections of weeks regardless any other selection in the dashboard.
And for the dimension I just entered [DCE_IsoJaarWeek].
(I tried that earlier but with no success).
1 last addition to the above: I changed it to
=Sum({$<DCE_IsoJaarWeek={">=$(vMinWeek)<=$(vTodayWeek)"}, PRO_Domein={"MTV*"}>} [#CON_Controles])
because I want all other selections to apply but have the week in the chart visible irrespective of any time/week selections made in the dashboard.
I would like to understand the difference between:
=Sum({$<DCE_IsoJaarWeek={">=$(vMinWeek)<=$(vTodayWeek)"}, PRO_Domein={"MTV*"}>} [#CON_Controles])
=Sum({1<DCE_IsoJaarWeek={">=$(vMinWeek)<=$(vTodayWeek)"}, PRO_Domein={"MTV*"}>} [#CON_Controles])
Because with the first, when I select the year 2024, I only get the 2024 weeks ending with the max. (vWeekToday variable). When I select 2022 I only get the weeks in 2022 within the vMinWeek and vTodayWeek range.
But with the second (=Sum({1<DC) all filters are ignored? Or only the time filters? Because when I use the 1 identifier, nothing in the chart changes when I set some other filters/selections.
I have read the Qlik documentation about set expressions but it did not answer my question if all filters are ignored or only the mentioned variables, in my case DCE_IsoJaarWeek and PRO_Domein.
Or is it because I used the variable and is the variable based on the filtered selection?
Even using the dollar expansion to have the values calculated in the set expression doesn't make a difference (=Sum({$<DCE_IsoJaarWeek={">=$(=((Year(Today())-2)*100)+week(today()))<=$(=((Year(Today())*100)+week(Today())-1))"}, PRO_Domein={"MTV*"}>} [#CON_Controles]))
Let's say I have 4 filters possible for the sheet. I want 2 of these to be ignored by the chart (DCE_IsoJaarWeek and Pro_Domein), but the other 2 (let's say 2 of the variables below) should affect the chart.
Is this possible and how?