topic Set Analysis Syntax in QlikView

Set Analysis Syntax

Tue, 29 Oct 2013 15:21:33 GMT

I have a pie chart that is supposed to break down Passing, Failing and Excluded for the most recent report End Date.

I am using the expression:

count({<P219A_STATUS_C={1},[END DATE]={"=$(Max([END DATE]))"}>}P219A_STATUS_C)

P219A_Status_C:

1= Passing

2= Failing

3= Excluded

The Max(End Date) = 10/28/2013

When I use count({<P219A_STATUS_C={1}>}P219A_STATUS_C) and select the end date from a list box my results show 63 passing but when I try to include max(Date) in the set analysis my result shows passing as 0

Any ideas on my syntax error?

Thanks

Re: Set Analysis Syntax

jonathandienst — Tue, 29 Oct 2013 15:26:10 GMT

If END DATE is a date field, you may need:

[END DATE]={"$(=Date(Max([END DATE])))"}

HTH

Jonathan

Re: Set Analysis Syntax

Tue, 29 Oct 2013 15:30:20 GMT

Thank you that did the trick. I always have a hard time with dates inside of a set analysis.

Re: Set Analysis Syntax

Tue, 29 Oct 2013 15:41:40 GMT

Hi Jonathan, could you help me one more time. I am using the updated version of the set analysis:

count({1<P219A_STATUS_C={1},[END DATE]={"$(=Date(Max([END DATE])))"}>}P219A_STATUS_C)

However it is not ignoring my selections, do you know why that would happen?

Thanks

Re: Set Analysis Syntax

swuehl — Tue, 29 Oct 2013 15:58:28 GMT

You'll probably need to tell QV that the max([END DATE]) should use the set identifier 1:

count({1<P219A_STATUS_C={1},[END DATE]={"$(=Date(Max({1} [END DATE])))"}>}P219A_STATUS_C)

Re: Set Analysis Syntax

Fri, 01 Nov 2013 15:08:23 GMT

Hi Jonathan,

I swear I am going crazy. I was using the expression you helped me with the other day and it was working perfectly. Overnight our dashboard updated and now it is not working. My pie charts are displaying 'No data to display'. I am pretty confident it is not the expression that broke but I was wondering if you have ever run into anything like this and/or had an idea?

Thanks

Re: Set Analysis Syntax

swuehl — Fri, 01 Nov 2013 15:20:50 GMT

Hi Katie,

it might be just coincidential, but we just had a month change.

To investigate why you don't see any data, you can start by creating an expression

count(P219A_STATUS_C)

then select 1 in P219A_STATUS_C and then select today / yesterday in END DATE.

What do you get?

Re: Set Analysis Syntax

Fri, 01 Nov 2013 16:14:22 GMT

Thanks swuehl,

I have gone through the load script and the set analysis piece by piece and everything seems to be working if I remove that one part of the set analysis. It also works if I remove that from the set analysis and I use an end date list box to select Max(End Date).

I am just confused as to why an expression would change overnight!

Very Confused!

Can you think of another way to get to the same result.... Max(End Date)?

Re: Set Analysis Syntax

swuehl — Fri, 01 Nov 2013 16:40:19 GMT

Hm, is your END DATE field in fact a timestamp? What do you get by using

=num(max([END DATE]))

in a text box?

Re: Set Analysis Syntax

Fri, 01 Nov 2013 16:42:03 GMT

I get 41579

Re: Set Analysis Syntax

swuehl — Fri, 01 Nov 2013 16:46:53 GMT

What is your standard date format? Or, if you put this in the text box:

=date(41579)

What exactely do you get? Compare this to the max date in the END DATE list box. Even a difference like 11/1/2013 vs 11/01/2013 does matter here.

Re: Set Analysis Syntax

Fri, 01 Nov 2013 16:55:24 GMT

ahh ha... it is different

=date(41579) .... 11/1/2013

Max End Date is 11/01/2013

So then would I have to use...

({1<P219B_STATUS_C={1},[END DATE]={"$(=Date(Max({1}[END DATE])),'MM/DD/YYYY')"},MU_EP_Flag={'Y'}>}P219B_STATUS_C)

That doesn't seem to be working...

Thank you SOOOOOO much for your help. I am going to try to attach a file to my original question to see if anyone has time to take a look!

Re: Set Analysis Syntax

swuehl — Fri, 01 Nov 2013 16:58:32 GMT

That would explain your issue, though.

In above expression, I think you have one closing bracket too much after [END DATE].

If this is your real expression and not a copy&paste issue, try correcting the field modifier.

Re: Set Analysis Syntax

Fri, 01 Nov 2013 17:13:38 GMT

good catch but same results!