Using Weekname with Set Analysis in Chart Expression

[mjt_childrens]

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......

[#2]

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

[mjt_childrens]

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

[jonathandienst · Accepted solution]

Martha

You could try this small variation:

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

Jonathan

[mjt_childrens]

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?

[jonathandienst]

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