Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Function within a Set Analysis

I'm new to using a set analysis and have watched a few videos, but am still having a problem getting this to work correctly.  I'm using QlikView 12, and I'm wanting to get a count of values from a year prior to the date.  Here is the formula I'm using:

Count({$<[Date]={"$(=addyears([Date],-1))"}>}[Date])

So I'm trying to get something that lists my Date, the Date a year ago, the count of values for the Date, and the count of values for a year ago from the Date.  Any help as to what I'm doing wrong would be greatly appreciated.

12 Replies
Highlighted

Re: Function within a Set Analysis

Expression looks fine ...whats the error your getting ? could share the sample app ?

Highlighted
Creator III
Creator III

Re: Function within a Set Analysis

have you tried set analysis builder app

Set Analysis Wizard for QlikView | qlikblog.at

Highlighted

Re: Function within a Set Analysis

Check whether you have data for that date in the last year ?

Highlighted
Anonymous
Not applicable

Re: Function within a Set Analysis

what's your date format?, are your dates dual() ?

Highlighted

Re: Function within a Set Analysis

Few things here

1) May be you are not selecting a single date, or even if you are, there is no harm in adding a Max() or Min() function based on the requirement

Count({$<[Date]={"$(=AddYears(Max([Date]), -1))"}>}[Date])

2) May be the date format set through environmental variables are different then the date format for your field Date. So, it might help to add Date() function with the correct date format for Date field

Count({$<[Date]={"$(=Date(AddYears(Max([Date]), -1), 'DD/MM/YYYY'))"}>}[Date])

3) Are you making selections in other date and time related fields such as Year or Month, it might make sense to ignore selections in those fields as Date will be driving your selection here

Count({$<[Date]={"$(=Date(AddYears(Max([Date]), -1), 'DD/MM/YYYY'))"}, Year, Month, MonthYear, Quarter, QuarterYear>}[Date])

Highlighted
Contributor II
Contributor II

Re: Function within a Set Analysis

I'm not getting an error, but all of my values are 0.  I can replace the $(=addyears([Date],-1)) section with a static date and it returns the result I would expect.  The .qvw file has sensitive data in it and I'm not sure how I could strip that out to share.

Highlighted
Contributor II
Contributor II

Re: Function within a Set Analysis

Date format is dd/mm/yyyy.  Not sure what the dual() means.

Highlighted
Contributor II
Contributor II

Re: Function within a Set Analysis

I do have data, and as mentioned on my other reply, it returns the correct data when I enter a static date.

Highlighted
Contributor II
Contributor II

Re: Function within a Set Analysis

I appreciate the suggestions, but none of those worked.  They still just returned zero values.