Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Expression looks fine ...whats the error your getting ? could share the sample app ?
have you tried set analysis builder app
Check whether you have data for that date in the last year ?
what's your date format?, are your dates dual() ?
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])
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.
Date format is dd/mm/yyyy. Not sure what the dual() means.
I do have data, and as mentioned on my other reply, it returns the correct data when I enter a static date.
I appreciate the suggestions, but none of those worked. They still just returned zero values.