topic Re: Date Comparison - SET ANALYSIS in QlikView

Date Comparison - SET ANALYSIS

Anonymous — Thu, 22 Jan 2015 18:40:45 GMT

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

Re: Date Comparison - SET ANALYSIS

Gysbert_Wassenaar — Thu, 22 Jan 2015 19:50:35 GMT

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.

Re: Date Comparison - SET ANALYSIS

Anonymous — Thu, 22 Jan 2015 20:07:52 GMT

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

Re: Date Comparison - SET ANALYSIS

Gysbert_Wassenaar — Thu, 22 Jan 2015 20:14:47 GMT

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)))"}

Re: Date Comparison - SET ANALYSIS

Anonymous — Thu, 22 Jan 2015 20:33:49 GMT

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

Best Regards,

Shree Angane