topic Re: Need help involving date field in set analysis expression in QlikView

Need help involving date field in set analysis expression

mgavidia — Wed, 06 Feb 2013 19:51:37 GMT

Hello everyone,

I hope someone in the community is able to assist me with this problem.

I need to come up with an expresion that will give me the total count of records where:

- UnitStatus is "Rented'

- Category is either RV" or "Office spaces"

- MoveOutDate is blank (that is, there is no date stored in the field. It is a 'date' field)

This expression below gives me the first 2 requirements but I cannot come up with the right expression for the date.

How can this Set Analysis expression by modified to tell QlikView to also exclude records where the date is blank?

Sum ({$<UnitStatus={'Rented'},Category={"RV*","Office Spaces"}>}#MoveInCounter)

I'll appreciate any tips on this matter.

Thanks,

Miguel Gavidia

jagan — Wed, 06 Feb 2013 19:59:45 GMT

Hi,

Try this

=Sum ({$<UnitStatus={'Rented'},Category={"RV*","Office Spaces"}, MoveOutDate-={'*'}>}#MoveInCounter)

Qlikview shows error new -=, but it works as expected.

Hope this helps you.

Regards,

Jagan.

mgavidia — Wed, 06 Feb 2013 20:37:28 GMT

Jagan,

Well, I did try that previously ( * with double quotes) and it gave me all 0s.

That's why I think I am using the wrong syntax. I cannot find any samples of set analysis using dates in this way...

I also tried Stefan's set analysis wizard but no luck.

=Sum ({$<UnitStatus={'Rented'},Category={"RV*","Office Spaces"}, MoveOutDate-={"*"}>}#MoveInCounter)

Thanks for your reply.

Miguel