Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
then convert variable values to num and then try.
ex:
Sum({$<DATE_NO={">=$(=num($(v1stDaySelectedWeek))) <=$(=num($(vLastDaySelectedWeek)))"}>} GROSS_QTY)
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.
You missed the Equal Operator, try with sfatoux72 example
Sum({$<DATE_NO={">=$(=v1stDaySelectedWeek) <=$(=vLastDaySelectedWeek)"}>} GROSS_QTY)
try
Sum({$<DATE_NO={">=$(=date($(v1stDaySelectedWeek))) <=$(=date($(vLastDaySelectedWeek)))"}>} GROSS_QTY)
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
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.
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.
then convert variable values to num and then try.
ex:
Sum({$<DATE_NO={">=$(=num($(v1stDaySelectedWeek))) <=$(=num($(vLastDaySelectedWeek)))"}>} GROSS_QTY)
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