topic Expressions inside set analysis in QlikView

Expressions inside set analysis

Anonymous — Thu, 14 May 2015 13:43:48 GMT

Hi everyone,

I'm new to Qlikview and have been trying to work with the set analysis but I can't make it work the way I want it.

I'd like to do a count of subscriptions which where done in the last 7 days without making any selection.

I went for:

=count ( {<(today()-[Action Date]) = {1,2,3,4,5,6,7}>} [Subscription ID])

But it doesn't seem to be working. Is there a problem with the expression in the left?

Thanks!

Re: Expressions inside set analysis

Miguel_Angel_Baeyens — Thu, 14 May 2015 13:51:21 GMT

Hi Fernando,

Yes, the left part in the filter of a set modifier must always be a field. Not a variable, another function or expression but a valid field in the data model.

Although I'm not sure how your model is created, the expression syntax you are looking for to get the subscriptions of the past 7 days would be

Count({< DateField = {"=>$(=Date(Today() -7))<=$(=Date(Today()))"} >} [Subscription ID])

Provided Date(DateField) and Date(Today() -7) are returning the same result format (test it in a text object) the the Count() aggregation will work.

Miguel

Re: Expressions inside set analysis

Anonymous — Thu, 14 May 2015 15:48:47 GMT

Hi Miguel,

Thanks for your help, I didn't know about that. The problem is not solved though. Let me explain a bit further:

I have 2 Date fields. One is called "Calendar Date", and the other is called "Action Date". Calendar Date is the date the subscription was purchased. Action Date is the date in which the user did something to his subscription (i.e. cancel it). I want to see subscriptions that were done a particular action (ID 12) in the last 7 days (no matter when the subscription has purchased).

By doing:

Count({< [Action Type ID] = {12}, [Action Date] = {"=>$(=Date(Today() -7))<=$(=Date(Today()))"} >} [Subscription ID])

I still get no results. I have double checked and some should appear.

Do you have any further advise?

Thanks a lot!

Re: Expressions inside set analysis

Miguel_Angel_Baeyens — Thu, 14 May 2015 17:29:28 GMT

Make sure that [Action Date] and Date(Today()) return the same format date. This is not usually the case, since one date can have some decimals and the other is a round number, for example.

Check the app in here:QlikView App: Dates, Date Ranges and Set Analysis

Miguel