Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Expressions inside set analysis

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!

Tags (1)
3 Replies
Highlighted

Re: Expressions inside set analysis

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

Highlighted
Contributor II
Contributor II

Re: Expressions inside set analysis

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!

Highlighted

Re: Expressions inside set analysis

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