Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi Raman,
They are different fields so why not use both in the same filter?
HTH - Daniel.
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
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
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.
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
It worked!
Thank you Lech.
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.
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
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