Year Filter

[Anonymous]

Hi,

I am having an issue applying a year filter in NPrinting.

I have two dates columns but need the year filter to filter on both of these. Below are the two column I have in the data load editor but these are in the same SQL script. These dates refer to orders created and orders shipped.

Column 1: DATE(MonthStart([Created]), 'YYYY') as CreatedYear

Column 2: DATE(MonthStart([OutTimestamp]), 'YYYY') as OutTimestampYear

LOAD [LocationPartID],

           [TotalQty],

          Created,

          DATE([Created],'DDMMMYYY') as Created,

          DATE(MonthStart([Created]), 'YYYY') as Year,

          [OutTimestamp],

          DATE(MonthStart([OutTimestamp]), 'YYYY') as OutTimestampYear

FROM [Inventory.qvd]

(qvd);

I am not able to alias both of these columns as 'Year' as this will not be accepted, so if there are way around this?

Thank you in advance for any help.

[Daniel_Jenkins]

Hi Raman,

They are different fields so why not use both in the same filter?

HTH - Daniel.

[Anonymous]

Hi Daniel,

Thanks for the response.

I tried this but data still doesn't filter, not sure if it's down to column format?

DATE(MonthStart([Created]), 'YYYY') as CreatedYear

DATE(MonthStart([OutTimestamp]), 'YYYY') as OutTimestampYear

[Lech_Miszkiewicz · Accepted solution]

Hi Raman,

Yes - this is definitely due to wrong format

What you are using is wrong as possibly you are creating 12 values for each Year due to used expression. Although you might see the same text for each value underlying numerical representation of this value will be different (each representing month start of given date).

Instead i would suggest to use in your Qlik data model:

YEAR([Created]) as CreatedYear

YEAR([OutTimestamp]) as OutTimestampYear

and then apply method sugested by Daniel

cheers

Lech

[Anonymous]

Hi Lech,

I have updated the data load editor as suggested and added two filters in NPrinting but the report still does not filter. The report is still returning data for 2017.

[Lech_Miszkiewicz]

it should be:

Numerical Value = 2018

If this still does not work then we need to look at your app and establish how is your script created, what datamodel it creates and how it interacts with UI as there is too many different things which need to be set up properly to make it work.

cheers

Lech

[Anonymous]

It worked!

Thank you Lech.

[Anonymous]

Hi Lech,

If I wanted to add a month filter would I just follow the same process, but use 'value is' in the filter as it's not a numerical value.

[Lech_Miszkiewicz]

Yes an No!

Yes-you can follow the same principal

No-month is not a text!! It is a dual value. Dual meanin: text (such as Jan, Feb etc...) but actual representation is numerical 1 for Jan, 2 for Feb etc...

So you would have to filter it by numerical value and assign number from 1 to 12

Cheers

Lech

[Anonymous]

Thank Lech.

I have noticed an issue with the year filter I have added. It does work but as I have two filter years it seems to be affecting the data. When I add both filters it seems to be removing data from one of the tables.

YEAR([Created]) as CreatedYear

YEAR([OutTimestamp]) as OutTimestampYear

When I add a filter for OutTimestamp it removed results from the Created table. Is there a way to have these filters on specific excel sheets or tables?

Table 1: Created Year = 2018

Table 2: OutTimestamp Year = 2018

OR

Sheet 1: Created Year = 2018

Sheet 2: OutTimestamp Year = 2018