topic Set Analysis using a RANGE of weeknames in QlikView

Set Analysis using a RANGE of weeknames

picturebox — Fri, 06 Nov 2009 20:03:09 GMT

Hello Community,

I am battling to get a range accepted in my set analysis. Using an if it looks like this:

sum(if ((CW<='$(vMaxCW)') and (CW>='$(vMaxCW25)'), UnitMovement

))

where CW is in form of "weekname", i.e. 2009/36. SA is just so much faster ...

Can anyone help?

Best Regards,

Petra

Set Analysis using a RANGE of weeknames

Fri, 06 Nov 2009 21:38:49 GMT

Greater than and less than are not allowed where you are putting them. In that place you are indicating what to do with the set, only =, -=, *=, /= (I think) are allowed. In order to use a range, you still want =, because you want to include the set. You should also be able to ditch the if.

This should work:

sum({<CW={"<=$(vMaxCW)>=$(vMaxCW25)"}>} UnitMovement)

EDIT: You need to include both in one set in order to make it an and and force both conditions.

Set Analysis using a RANGE of weeknames

picturebox — Fri, 06 Nov 2009 22:10:40 GMT

Hi,

thanks for the answer. This does not work and I think it is because the vMaxCW variables are not correctly syntaxed and thus addressed to return the value. Normally you would use either '$(vMaxCW)' or "$(vMaxCW)" (double or single quotes) but in this SA it does not want to accept that syntax ...

Petra

Set Analysis using a RANGE of weeknames

picturebox — Fri, 06 Nov 2009 22:12:30 GMT

I am using 8.5 ...

Set Analysis using a RANGE of weeknames

Fri, 06 Nov 2009 23:10:16 GMT

Could you give me an example of what your CW field contains and the values of your MaxCW variables?

I tend to get confused with which quotes to use. I've been able to use double-quotes and then use single quotes within. Something like:

sum({<CW={"<='$(vMaxCW)'>='$(vMaxCW25)'"}>} UnitMovement)

If your variables are text values, you could probably cheat by adding the single quotes into the variable definition. Really, it is all just speculation, because I don't know what your data looks like.

Set Analysis using a RANGE of weeknames

picturebox — Fri, 06 Nov 2009 23:38:24 GMT

the CW (Calendar Week) would be a weekname i.e. 2009/26 ...the variables are set using

weekname( MaxSalesWeek)

where weekname returns a format like: 2009/10 i.e. year/weeknumber.

Set Analysis using a RANGE of weeknames

Fri, 06 Nov 2009 23:56:13 GMT

Have you verified that QlikView can evaluate >= a week like that?

I think the problem is that the variable is probably being returned as a string, while your CW field is probably a form of date field or a string.

You may need to convert the variables to be dates, so QlikView can evaluate the condition. Try putting your expression into a chart, but don't give the expression a label. Then when the chart is rendered, you should see the full expression with the variables in place. Are you getting {''} for your sets or is the variable values in there? Also, you should try hardcoding the values of those variables in and see if that works.

The Set Analysis syntax is correct, I think you are having issues with data types, that's why it's not working. I usually use 200910 for my weeks and that format can easily be evaluated as a number for comparison.

Set Analysis using a RANGE of weeknames

picturebox — Sun, 08 Nov 2009 19:19:16 GMT

I haven't played around with it any more right now, but I wonder why it works fine in an if statement, which I am currently using instead of the set analysis. If I need to change all the week dates back to numbers i.e. 200910, I will need to rerun the script which I cannot do currently as I don't have the source files, or can I concatenate to an existing table? Gets messy!

Set Analysis using a RANGE of weeknames

Tue, 10 Nov 2009 00:01:17 GMT

I'm not sure why it would work in the if and not Set Analysis.

First, I would verify that the variables are coming out as you would expect them to. Make a simple chart and use your Set Analysis expression as the expression, but don't give it a label. Then render the chart and your Set Analysis expression should be the label. Expand it, so you can see what your variables are coming out as. Make sure you see {'2009/10'} or whatever they are in the expression.

You should also try to hardcode the values of your variables into a Set Analysis expression and see if you can get results. Does this work:

sum({<CW={"<='2009/15'>='2009/10'"}>} UnitMovement)

When I typed that out, it looks like my signs might have been switched.