Dealing with date format filter in QlikSense

[dialog_sistemi]

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

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

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

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)

[Anil_Babu_Samineni]

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)

[OmarBenSalem · Accepted solution]

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)

[dialog_sistemi]

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?

[OmarBenSalem]

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..