topic Re: Year Filter in Qlik NPrinting

Year Filter

Anonymous — Thu, 19 Jul 2018 08:11:38 GMT

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.

Re: Year Filter

Daniel_Jenkins — Thu, 19 Jul 2018 08:30:09 GMT

Hi Raman,

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

HTH - Daniel.

Re: Year Filter

Anonymous — Thu, 19 Jul 2018 14:12:44 GMT

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

Re: Year Filter

Lech_Miszkiewicz — Fri, 20 Jul 2018 00:27:55 GMT

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

Re: Year Filter

Anonymous — Mon, 23 Jul 2018 08:30:02 GMT

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.

Re: Year Filter

Lech_Miszkiewicz — Mon, 23 Jul 2018 08:36:41 GMT

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

Re: Year Filter

Anonymous — Mon, 23 Jul 2018 09:05:06 GMT

It worked!

Thank you Lech.

Re: Year Filter

Anonymous — Mon, 23 Jul 2018 09:31:16 GMT

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.

Re: Year Filter

Lech_Miszkiewicz — Mon, 23 Jul 2018 09:42:39 GMT

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

Re: Year Filter

Anonymous — Mon, 23 Jul 2018 13:14:32 GMT

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

Sheet 1: Created Year = 2018

Sheet 2: OutTimestamp Year = 2018

Re: Year Filter

Lech_Miszkiewicz — Mon, 23 Jul 2018 13:32:35 GMT

Well

This comes down to your data model in Qlik app.

It is not Nprinting related question.

You will have to provide us with information about your script and model. To be frank this topic is not a place for this conversation since it goes of the track.

Basicly you need to get your Qlik application right in first place.

All nprinting filters are just like listbox selections so before you come to Nprinting make sure you check how they will work directly in Qlik.

Cant help more here

Cheers

Lech