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.

[JonnyPoole]

Your problem sounds solvable but I'm not clear on the data structure. Can you share an example of the data that shows the week number data values next to the dca_datum data values and show clearly the maximum week number that should be used for your needs (based on the data)? 

[DutchArjo]

This is how the weeknumbers look like (DCE_Jaarweeknummer)

Below is how the data/time part of the table looks like:

Variables in this part are:

*DCA_Date

*DCA_IsoJaarWeek

*DCA_time

*DCE_Date

*DCE_IsoJaarWeek

*DCE_time

DCA = Date Controle Aanvang (aanvang = start)

DCE = Date Controle Einde (einde = end)

The maximum value today should be 202414 (and the minimum should be 202215).

[JonnyPoole · Accepted solution]

Its difficult to understand the details .  I *think* you are trying to filter based on the "DCE_Iso_JaarWeek" field. Is that correct?  I ask because the column headers are cut off  in the screenshot you shared and you mentioned "DCE_Jaarweeknummer" in the text of your reply.

If my guess is accurate then I think you should approach this using variables. Create two variables for todays week and the minimum week as possible. In my approach, please note that the variables are designed to return the week values in purely numeric only form  (YYYYMM):

vTodayWeek

=Year(Today()) * 100 + Week(Today()))

vMinWeek

=Min(DCE_Iso_JaarWeek)

When you define these two variables in your app, ensure to preserve the preceding '=' sign. That way the weeks are pre-calculated for the use in SET ANALYSIS.

For the set modifier, I think you can use a search mask with the variables employed as follows which should work provided the values are purely numeric

{<DCE_Iso_JaarWeek={">=$(vMinWeek)<=$(vTodayWeek)"}>} 

I am not sure which aggregation function you intend to use nor which field you intend to aggregate but if you were for example trying to do Sum(Sales) then the complete expression would be: 

sum(  {<DCE_Iso_JaarWeek={">=$(vMinWeek)<=$(vTodayWeek)"}>}   Sales) 

[DutchArjo]

but to use this approach I need to use the data load editor right?

For this particular dashboard, this is managed by a seperate, dedicated department.

Or could I integrate vTodayWeek and vMinWeek into the set analysis formula?

I am indeed using the sum aggregate function, summing the number of checks (# controles).

edit: I found the right button to create a variable. I've created vMinWeek now this way:

=(Year(Today())-2) * 100 + (Week(Today())+1)

The variables calculate the correct values but the set expression still returns an error:

I have no clue what's going wrong here.

[JonnyPoole]

What is the exact name of the data field? 

Is it DCE_IsoJaarWeek  or DCE IsoJaarWeek? 

Your screenshot uses the latter, but the line chart in your first posting shows the former on the x axis label.  

Whatever the name, try putting it in [] like one of the following: 

Sum(  {<[DCE_Iso_JaarWeek]={">=$(vMinWeek)<=$(vTodayWeek)"}>}  [# Controles] )

Sum(  {<[DCE Iso_JaarWeek]={">=$(vMinWeek)<=$(vTodayWeek)"}>}  [# Controles] ) 

[DutchArjo]

the exact name is DCE_IsoJaarWeek.

I looks like the expression is correct, the variable is in yellow, indicating that it is recognized, is it?

I put the dimension in brackets like you suggested but it still says the expression has an error:

It's nice Qlik says there is an error but it would be really helpful when Qlik would say where and what the error is.

I checked in the datamodelviewer for the specs of DCE_IsoJaarWeek:

I am wondering if the value type has something to do with the error from the set expression (if the type of the vMinWeek en vTodayWeek match with the type of DCE_IsoJaarWeek).

I also changed [# controles] for a different measure to see if that makes a difference, but it does not change the error notification.

Another thing I tried (knowing it does not working when selecting periods) is setting a limit for the dimension on the x-axis, just to see what happens:

But the result is still that the chart shows all the weeks available in the table/ DCE_IsoJaarWeek dimension:

[JonnyPoole]

I set up an app with integer values for DCE_IsoJaarWeek and it works fine.  There could be an issue with the datatype for DCE_IsoJaarWeek.  You could try to use a floor() function on the value in the load script to ensure its not a date in disguise. 

If you suspect SET ANALYSIS then try some variations without the variables: 

Hard coded variables:

Sum(  {<[DCE Iso_JaarWeek]={">=202215<=202414"}>}  [# Controles] ) 

Sum(  {<[DCE Iso_JaarWeek]={">=202215"}>}  [# Controles] ) 

Sum(  {<[DCE Iso_JaarWeek]={202215}>}  [# Controles] ) 

If these all work try with one variable:

Sum(  {<[DCE Iso_JaarWeek]={">=$(vMinWeek)"}>}  [# Controles] ) 

If this fails, send me a screenshot of your two variables. The names of the variable are case sensitive. 

[DutchArjo]

Even hard-coded the expression error remains:

here are screenshots of the created variables, but since even hard-coded the expression does not work, I assume the variables are not the issue here.

I will continue test the expression with just parts of the expression.

Hard coded variables:

Sum(  {<[DCE_IsoJaarWeek]={">=202215<=202414"}>}  [# Controles] ) 

Sum(  {<[DCE_IsoJaarWeek]={">=202215"}>}  [# Controles] ) 

Sum(  {<[DCE_IsoJaarWeek]={202215}>}  [# Controles] )

result:
none of these work. All return the expression error.

[JonnyPoole]

Ok. So its not the variables, but it could be the values. Lets try 'all values 'or * as well as a string value:

Sum(  {<[DCE_IsoJaarWeek]={"*"}>}  [# Controles] )

Sum(  {<[DCE_IsoJaarWeek]={'202215'}>}  [# Controles] )

Also here is the expression in my app that works: