topic Using Weekname with Set Analysis in Chart Expression in QlikView

Using Weekname with Set Analysis in Chart Expression

mjt_childrens — Thu, 29 Jul 2010 01:57:11 GMT

I'm struggling to get an expression to correctly summarize data in a chart using the weekname function. I have created a variable and assigned it like this:

varWeekName =

weekname(Today()-14)

it appears to be set to 2010/28. In my load script the field is created with the following line:

weekname(CRMTransDate) as CRMTranWeekYear

The expression in my chart is:

count({$<CRMTranWeekYear={$(varWeekName)}>} CRMRecType)

This returns null in my chart. If I display my data in a straight table it appears there is data where CRMTranWeekYear is 2010/28. What am I doing wrong? Am I using the weekname function incorrectly or is there a formatting issue between my variable and the field when I load my data?

Thanks for any help......

Using Weekname with Set Analysis in Chart Expression

Thu, 29 Jul 2010 02:51:43 GMT

You should check the format that you are getting for the CRMTranWeekYear by creating a list box (I usually do this for dates to see what is the format coming in from the external source).

Anyway, for the set analysis, you might want to try

count({$<CRMTranWeekYear={"=$(=varWeekName)"}>} CRMRecType)

Nimish

Using Weekname with Set Analysis in Chart Expression

mjt_childrens — Thu, 29 Jul 2010 03:22:00 GMT

Set analysis still has me confused.

I used your suggested formula and I get a result but not exactly what I'm expecting. The resulting count is over my entire current selection, not limited to the value set in the variable varWeekName. This is better than the null I was getting before.

I had added a list box for the CRMTranWeekYear and the values appear to be what I am expecting. There are values that should be matching the value set in my variable.

Martha

Using Weekname with Set Analysis in Chart Expression

jonathandienst — Thu, 29 Jul 2010 08:15:10 GMT

Martha

You could try this small variation:

count({$<CRMTranWeekYear={"$(varWeekName)"}>} CRMRecType)

Jonathan

Using Weekname with Set Analysis in Chart Expression

mjt_childrens — Thu, 29 Jul 2010 14:28:51 GMT

Thanks Jonathan, that was just the tweak I needed. The subtilties of set analysis give me heartburn. When you need extra quotes, extra equal signs, etc. I have not found documentation or a tutorial that explains this clearly -- do you know of any?

Using Weekname with Set Analysis in Chart Expression

jonathandienst — Thu, 29 Jul 2010 14:52:40 GMT

Not that well documented - and it sure gave me a headache trying to untangle the punctuation.

John Witherspoon has written quite a lot on this forum about set analysis, and I have found that very helpful. This one talks about the why and when of some of the punctuation: http://community.qlik.com/forums/t/28966.aspx

Keep trying!

Jonathan