Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for 
Search instead for 
Did you mean: 
dirk_fischer
Creator
Creator

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

1 Solution

Accepted Solutions
shraddha_g
Partner - Master III
Partner - Master III

then convert variable values to num and then try.

ex:

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

View solution in original post

8 Replies
sfatoux72
Partner - Specialist
Partner - Specialist

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.

Not applicable

You missed the Equal Operator, try with sfatoux72 example

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


shraddha_g
Partner - Master III
Partner - Master III

try

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

dirk_fischer
Creator
Creator
Author

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
Creator
Creator
Author

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
Creator
Creator
Author

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
Partner - Master III
Partner - Master III

then convert variable values to num and then try.

ex:

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

dirk_fischer
Creator
Creator
Author

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