8 Replies Latest reply: Nov 15, 2016 7:22 AM by Dirk Fischer

# Problem with set analysis and variables for between test

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

• ###### Re: Problem with set analysis and variables for between test

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.

• ###### Re: Problem with set analysis and variables for between test

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

• ###### Re: Problem with set analysis and variables for between test

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.

• ###### Re: Problem with set analysis and variables for between test

You missed the Equal Operator, try with Sébastien Fatoux example

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

• ###### Re: Problem with set analysis and variables for between test

try

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

• ###### Re: Problem with set analysis and variables for between test

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.

• ###### Re: Problem with set analysis and variables for between test

then convert variable values to num and then try.

ex:

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

• ###### Re: Problem with set analysis and variables for between test

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