All previous years compared to the selected year

[Anonymous]

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

[shraddha_g]

Try

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

[Anil_Babu_Samineni]

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

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

[sunny_talwar]

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)

[Anonymous]

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

[sunny_talwar]

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

[Anil_Babu_Samineni · Accepted solution]

May be this? There is exclude one called -

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

[Anonymous]

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