Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Date Comparison - SET ANALYSIS

Hi Guys,

I am using this below code to view DealId count for Current Year and Previous Year

Current Month:

=count({<MONTH=, Year={"=Date(max(Date),'YYYY')"}>} DealID) -- Works for Current

Previous Month:

=count({<MONTH=, Year={"=Date(AddYears(max(Date),-1),'YYYY')"}>} DealID) -- Does not work for previous.

Code works fine for Current Month. To get Previous Month I use AddYears(Max(Date),-1) code to fetch previous year.

Unfortunately Previous Month code does not work and give me Current Month Data.instead.....

Can somebody help me out with this.....AddYears stuff...

Regards,

Shree Angane

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

If you make a selection in the Date field you also need to 'reset' that selection in the set modifier:

=count({<Date=, MONTH=, Year={"=Date(AddYears(max(Date),-1),'YYYY')"}>} DealID)

And of course your Year field should be a date field since you're comparing it in the set modifier with a date, one that's formatted as a year, but still a date. If your Year field contains year values instead of dates then you need to use something like Year={"$(=Year(AddYears(max(Date),-1)))"}


talk is cheap, supply exceeds demand

View solution in original post

4 Replies
MVP & Luminary
MVP & Luminary

Year={"=Date(AddYears(max(Date),-1),'YYYY')"} selects the same month, but a year earlier. If you want the previous month you need to use the AddMonths function instead.


talk is cheap, supply exceeds demand
Contributor II
Contributor II

Apologies for Typo ... I actually meant

Current Year:

=count({<MONTH=, Year={"=Date(max(Date),'YYYY')"}>} DealID) -- Works for Current

Previous Year:

=count({<MONTH=, Year={"=Date(AddYears(max(Date),-1),'YYYY')"}>} DealID) -- Does not work for previous.

I get same output for both of these expressions

MVP & Luminary
MVP & Luminary

If you make a selection in the Date field you also need to 'reset' that selection in the set modifier:

=count({<Date=, MONTH=, Year={"=Date(AddYears(max(Date),-1),'YYYY')"}>} DealID)

And of course your Year field should be a date field since you're comparing it in the set modifier with a date, one that's formatted as a year, but still a date. If your Year field contains year values instead of dates then you need to use something like Year={"$(=Year(AddYears(max(Date),-1)))"}


talk is cheap, supply exceeds demand

View solution in original post

Contributor II
Contributor II

Gysbert.....you are fabulous ..... solution worked.....

Best Regards,

Shree Angane