Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
shree_angane
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
Gysbert_Wassenaar

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
Gysbert_Wassenaar

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
shree_angane
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

Gysbert_Wassenaar

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

shree_angane
Contributor II
Contributor II

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

Best Regards,

Shree Angane