7 Replies Latest reply: Dec 11, 2017 2:37 AM by Marco Ponzo

# All previous years compared to the selected year

Hi,

I'm Qlik beginner.

I have a table with a column nominated DTASPR (hire date), with many dates (DD/MM/YYYY).

I selected a Year (example 2007) to have a count of DTASPR (employee hired in that year):

Count (DTASPR.autoCalendar.Year)

and it's ok.

This expression give me all the EXCLUDED DTASPR, for past and later years:

Count ({1-\$} [DTASPR.autoCalendar.Year])

But I need the count of DTASPR only for PREVIOUS year (not later, example years<2007)

I don't know how modifier or identifier use.

Can you help me?

Thank's!

Marco

• ###### Re: All previous years compared to the selected year

Try

Count({<DTASPR.autoCalendar.Year = {"\$(=max(DTASPR.autoCalendar.Year)-1)"}>} DTASPR.autoCalendar.Year)

• ###### Re: All previous years compared to the selected year

Are you going to have <2007 if 2007 is an filter?

Count({<DTASPR.autoCalendar.Year = {"\$('<' & =max(DTASPR.autoCalendar.Year)-1)"}>} DTASPR.autoCalendar.Year)

• ###### Re: All previous years compared to the selected year

Not sure I understand what is needed, but you misplaced your = sign Anil. Also, adding an extra pair of parenthesis around Max(Field) - 1 to make sure it doesn't null out because of missing parenthesis...

Count({<DTASPR.autoCalendar.Year = {"\$(='<' & (Max(DTASPR.autoCalendar.Year)-1))"}>}DTASPR.autoCalendar.Year)

• ###### Re: All previous years compared to the selected year

WOW!!!! You are AMAZING!

Now second question... I have another column named URGE with values from 0 to 100, associated at DTASPR.

In the same previous year filter (ex.2007) I want to exlcude the DTASPR with URGE values > 0 (practically unlike zero). I need a further filter.

Thank's so much!!!!

• ###### Re: All previous years compared to the selected year

Which expression worked? Can you elaborate a little more on the second requirement? May be with a sample?

• ###### Re: All previous years compared to the selected year

May be this? There is exclude one called -

Count({<DTASPR.autoCalendar.Year = {"<\$(=Max(DTASPR.autoCalendar.Year-1)"}, DTASPR -= {"=Sum(URGE) >0"}>}DTASPR.autoCalendar.Year)

• ###### Re: All previous years compared to the selected year

It's perfect! Thank you so much Anil! Thank's all!