Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
Partner

Dealing with date format filter in QlikSense

Hi, I have 2 fields 'qty last day' and 'qty day before last'.

I'm using these variables and formulas:

QTY LAST DAY:

sum({<DATA , DATA_NUM={$(vmaxdata)}, AGGREGA_MISSIONE={'STOCCAGGIO'}>} QTY)

vmaxdata:  =(MAX(DATA_NUM))

QTY DAY BEFORE LAST:

sum({<DATA , DATA_NUM={$(vmaxdata2)}, AGGREGA_MISSIONE={'STOCCAGGIO'}>} QTY)

vmaxdata2:  =(MAX(DATA_NUM) -1)

These formulas works well if I filter DATA or DATA_NUM, but when I use a fomatted DATA it doesn't work anymore.

this is the table before filtering

table_data.png

filtering DATA it works, I have 2 rows as a result

filter_1.png

filtering Date(DATA,'DD/MM/YYYY') doesn't work. I have only 1 rows, why?

filter_2.png

I tried to insert this field in the set expression but it gives me an error

sum({<DATA ,Date(DATA,'DD/MM/YYYY'), DATA_NUM={$(vmaxdata2)}, AGGREGA_MISSIONE={'STOCCAGGIO'}>} QTY)

 

1 Solution

Accepted Solutions
Partner
Partner

Re: Dealing with date format filter in QlikSense

That's the normal behaviour.

to overcome this "issue"; create ur formated date field in the SCRIPT then add it in ur expression

1) in script: 

Date(DATA,'DD/MM/YYYY') as FormatedData

2) the expression:

sum({<FormatedData,DATA , DATA_NUM={$(vmaxdata)}, AGGREGA_MISSIONE={'STOCCAGGIO'}>} QTY)

View solution in original post

4 Replies

Re: Dealing with date format filter in QlikSense

You should use the format like, Your condition not satisfying? What are you trying to achieve with that?

sum({<DATA_NUM={"$(=Date(vmaxdata2,'DD/MM/YYYY'))"}, AGGREGA_MISSIONE={'STOCCAGGIO'}>} QTY)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Partner
Partner

Re: Dealing with date format filter in QlikSense

That's the normal behaviour.

to overcome this "issue"; create ur formated date field in the SCRIPT then add it in ur expression

1) in script: 

Date(DATA,'DD/MM/YYYY') as FormatedData

2) the expression:

sum({<FormatedData,DATA , DATA_NUM={$(vmaxdata)}, AGGREGA_MISSIONE={'STOCCAGGIO'}>} QTY)

View solution in original post

Partner
Partner

Re: Dealing with date format filter in QlikSense

Thanks, it works even if it seems more a workaround than a solution 😉

you are telling me that Qlik do not recognize the field inside the formula and that I have to prepare in the script every date format I need and I have to insert it into the set expression?

Partner
Partner

Re: Dealing with date format filter in QlikSense

Here's an illustrative example, hope u'll understand better:

Let's say, we have 2 date fields:

Date1 : MM/DD/YY

Date2: DD/MM/YY

+ Month and Year fields

What u want to do is:

If u select a Date; let's say : 15/03/2019, u return the  YTD-1 : 01/01/2018 to 15/03/2016

The exp would be sthing like:

sum({<Date1={"<=$(=addyears(max(Date1),-1))>=$(=AddYears(YearStart(max(Date1)),-1))"}>}Measure)

But now, imagine that u've selected a month, let's say: February:

Normally the Max(Date) of Feb in ur Data is 28/02/2019

so the expression would return:

sum({<Date1={<=28/02/2018>=01/01/2018}>}Measure)

But, that won't be the case, u know why?

Because when U select Feb, Qlik will ONLY see Feb associated Data.

so even though the expression might seem to return the YTD-1, since it sees only feb! it'll return data for 01/02/2018 to 28/02/2018 (the Feb Month)

So to tell Qlik, to only use the Month selection to know the Max(Date) we're working with but keep on seeing ALL THE DATE and not only FEB month; we add it to our expression:

from

sum({<Date1={"<=$(=addyears(max(Date1),-1))>=$(=AddYears(YearStart(max(Date1)),-1))"}>}Measure)

to:

sum({<Month,Date1={"<=$(=addyears(max(Date1),-1))>=$(=AddYears(YearStart(max(Date1)),-1))"}>}Measure)

Same logic on the Date2; we want to select Date2 to know the max(Date1) u're using in ur expression and have the min and max dates of ur YTD-1

If u do not add Date2 into ur expression and select Date2=02/28/2019

Qlik will only see that Date and thus it won't be able to calculate YTD-1..

Hope that helps..