Dates in Set Analysis

[samvile18]

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?

[SunilChauhan]

may this helps

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

[samvile18]

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)

[Anonymous · Accepted solution]

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)

[swuehl]

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

[samvile18]

Excellent, thanks for the help guys.

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