Qlik Sense line chart only to show weeknumbers from this week and max 2 years back

[DutchArjo]

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.

[DutchArjo]

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.

[JonnyPoole]

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. 

[DutchArjo]

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:

[DutchArjo]

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.

[DutchArjo]

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?