topic Dates in Set Analysis in QlikView

Dates in Set Analysis

samvile18 — Tue, 17 Jan 2012 11:55:35 GMT

Morning fellow QlikView enthusiasts,

I'm having a bit of a problem with some set analysis and I'm wondering if anyone can help me out??

I have two different date fields, Extract Date and Start Date. I want to be able to sum all of the value where these two dates are the same, I'm struggling with the syntax though.

I always seem to get 0 value back and I know there are at least 6 different occurances of the same date.

Can anyone help?

Dates in Set Analysis

SunilChauhan — Tue, 17 Jan 2012 12:02:38 GMT

may this helps

sum({<[Extract Date]={$(=only([Start Date]))}< }value )

Re: Dates in Set Analysis

samvile18 — Tue, 17 Jan 2012 12:55:49 GMT

Hello,

Thanks for the reply but it didn't quite work, I've included the actual syntax including the additional part you've suggested...I still can't get it to work though.

=sum({<[Extract Date]={$(=Date([Start Date]))}, [Sales Channel]={'FriendsLife'},[Conversion Status]={'2 Tender Doc Issued'}, Status={'Won','Live'}>}Value)

Re: Dates in Set Analysis

Anonymous — Tue, 17 Jan 2012 13:18:58 GMT

Try putting quotes around the dollar expansion:

=sum({<[Extract Date]={'$(=Date([Start Date]))'}, [Sales Channel]={'FriendsLife'},[Conversion Status]={'2 Tender Doc Issued'}, Status={'Won','Live'}>}Value)

Dates in Set Analysis

swuehl — Tue, 17 Jan 2012 14:54:52 GMT

Do you have a unique key per record, like ID? If not, could you create a unique key in your load (e.g. using recno() )?

Then you could it like:

=sum({<ID={"=[Start Date]=[Extract Date]" }, [Sales Channel]={'FriendsLife'},[Conversion Status]={'2 Tender Doc Issued'}, Status={'Won','Live'}>}Value)

i.e. you select on your record IDs, where [Start Date] value is equal to [Extract Date] value, using a search expression.

Hope this helps,

Stefan

Dates in Set Analysis

samvile18 — Wed, 18 Jan 2012 10:05:34 GMT

Excellent, thanks for the help guys.

Aaron...you're solution is working a treat