Problem with set analysis and variables for between test

[dirk_fischer]

Hi everybody,

I pretty new to QlikSense and struggling with a set analysis problem, which is probably pretty easy to solve. But reading the discussion in the community didn't help, I'm just squashing my head on the wall.

I have a graph, where I show the weekly production output for a Job since the start of the Job. If a week on the graph is selected, I show the output for the job up to this week. This is working fine.

Now I want to show the daily output for the selected week or if nothing is selected, Show the daily output for the latest week.

If I use the formula like below, everything is working fine.

Sum({$<DATE_NO={">=42275 <=42281"}>} GROSS_QTY)

DATE_NO is an integer value for the production days and I filled up the missing days, so I have continuous datapoints for the whole Job.

Now if I try to use variables, it looks like the selection is ignored, although the variables contain the correct values.(displaying them in the same table proves the fact). I have a variable vMaxDate, which stores the biggest selected date or the Overall Maximum, if no week is selected. From this variable, I calculate the 1st day of the week, with $(vMaxDate) - WeekDay(vMaxDate, 0), for the last day I add 6 to the 1st day of the week. Using the variables in a table displays the correct values and if I use the fixed expression, the table is reduced to 3 rows, as expected. If I use a variable expression, I get all rows in the table displayed.

I tried

Sum({$<DATE_NO={">=$(v1stDaySelectedWeek) <=$(vLastDaySelectedWeek)"}>} GROSS_QTY)

as well as

Sum({$<DATE_NO={'>=$(v1stDaySelectedWeek) <=$(vLastDaySelectedWeek)'}>} GROSS_QTY)

and all other kinds of Variation I found a proposed solution in other discussions.

I also  tried the set analysis wizard from Stephan Walter, but it did not work for me.

Does anybody see, what's wrong?

I would really appreciate, if somebody has a proposal, because I start getting bored from the squishy sound of my head banging on this wall.

Regards,

Dirk

[sfatoux72]

Try this :

Sum({$<DATE_NO={">=$(=v1stDaySelectedWeek) <=$(=vLastDaySelectedWeek)"}>} GROSS_QTY)

And for information, you could use the function WeekStart(date) and WeekEnd(date) to have directly the fist day of the week and the last day of the week.

[#3]

You missed the Equal Operator, try with sfatoux72 example

Sum({$<DATE_NO={">=$(=v1stDaySelectedWeek) <=$(=vLastDaySelectedWeek)"}>} GROSS_QTY)

[shraddha_g]

try

Sum({$<DATE_NO={">=$(=date($(v1stDaySelectedWeek))) <=$(=date($(vLastDaySelectedWeek)))"}>} GROSS_QTY)

[dirk_fischer]

Hi Sébastien,

thank you very much for your suggestion. Unfortunately, it does not help. It still ignores the selection as soon as I switch to the variable expression.

It behaves as if there aren't any values matching the criteria.

The hint about Weekend / Weekstart is quite good, I implemented it direcly for calculating the variables of the interval. Thank's a lot. I also tried to use it in the set analysis expression, but no success.

Regards

Dirk

[dirk_fischer]

I tried the Equal Operator (had tried it before), but it doesn't help. I also tried with single Quote ' instead of double Quote ", but this als didn't help.

[dirk_fischer]

DATE_No is an integer, so converting the variables into a date doesn't help. I gave it a try anyway, but not luck.

Thank you for the Suggestion.

[shraddha_g · Accepted solution]

then convert variable values to num and then try.

ex:

Sum({$<DATE_NO={">=$(=num($(v1stDaySelectedWeek))) <=$(=num($(vLastDaySelectedWeek)))"}>} GROSS_QTY)

[dirk_fischer]

That was IT!!! Thank you so much for your help. Finally it's working. It's also working, if you just refer to the variable.

Sum({$<DATE_NO={">=$(=$(v1stDayOfSelectedWeek))<=$(=$(vLastDayOfSelectedWeek))"}>} GROSS_QTY)

Regards

Dirk