topic Re: All previous years compared to the selected year in QlikView

All previous years compared to the selected year

Anonymous — Thu, 30 Nov 2017 11:27:48 GMT

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

shraddha_g — Fri, 01 Dec 2017 04:16:07 GMT

Try

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

Re: All previous years compared to the selected year

Anil_Babu_Samineni — Fri, 01 Dec 2017 05:23:17 GMT

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

sunny_talwar — Fri, 01 Dec 2017 05:40:24 GMT

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

Anonymous — Fri, 01 Dec 2017 11:48:53 GMT

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

sunny_talwar — Fri, 01 Dec 2017 16:39:44 GMT

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

Anil_Babu_Samineni — Sat, 02 Dec 2017 01:27:34 GMT

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

Anonymous — Mon, 11 Dec 2017 07:37:09 GMT

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