Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Sum where the date is lower than today's date minus 1 year

Hello Experts

I am trying to make a field to display the sum of the sales in which the date is lower than today's date minus one year. So far I have been able to write this function:

Sum({<[Date field]={"<=$(Addyears(Today(),-1)"}>} Sales)


But it is not working as it is displaying a null value.


In addition have to say that this may be pretty obvious for sure, but I am kind of new on learning Qlik Sense.


Thanks in advance and regards

1 Solution

Accepted Solutions
MVP
MVP

Re: Sum where the date is lower than today's date minus 1 year

Do you need to add Date formatting as Gysbert mentioned in his first comment?

Sum({<[Date field]={"<=$(=Date(Addyears(Today(),-0),'DD.MM.YYYY'))"}>} Sales)


Make the part in red match the format of your [Date field]


Why don’t my dates work?

10 Replies

Re: Sum where the date is lower than today's date minus 1 year

Try Sum({<[Date field]={"<=$(=Addyears(Today(),-1))"}>} Sales). If the date format of your date field differs from the document default you may need to use the date function to add a format. For example Sum({<[Date field]={"<=$(=Date(Addyears(Today(),-1),'DD.MM.YYYY'))"}>} Sales).


talk is cheap, supply exceeds demand
Not applicable

Re: Sum where the date is lower than today's date minus 1 year

Hello Gysbert

Lot of thanks for your help. By adding the modifications you propose to the query it is now displaying "0 EUR" value, which is not correct.

I also have tried to change the function to:

Sum({<[Date field]={"<=$(=Addyears(Today(),-0))"}>} Sales)

Just to check if this returns the same value than  Sum(sales) (I understand it should). But it also displays zero value, while Sum(sales) it is displaying a non zero value.

Regards

MVP
MVP

Re: Sum where the date is lower than today's date minus 1 year

Which dimensions are you using in your chart, what is the field format of [Date field] and do you have anything else selected?

Re: Sum where the date is lower than today's date minus 1 year

Can you post a small qlikview document that demonstrates the problem.


talk is cheap, supply exceeds demand
MVP
MVP

Re: Sum where the date is lower than today's date minus 1 year

Do you need to add Date formatting as Gysbert mentioned in his first comment?

Sum({<[Date field]={"<=$(=Date(Addyears(Today(),-0),'DD.MM.YYYY'))"}>} Sales)


Make the part in red match the format of your [Date field]


Why don’t my dates work?

MVP
MVP

Re: Sum where the date is lower than today's date minus 1 year

You basically need to match the field format within the numerical search expression:

Dates in Set Analysis

And if your dimension is "Date field", you won't see the numbers on e.g. today's line unless you are using the TOTAL qualifier (maybe use the TOTAL qual. field list to consider other dimensions).

And if you made a selection in a calendar field, e.g. MonthYear, this might interfere with the set modifier,so you might need to clear possible user selections in these fields:

Sum(TOTAL {<[Date field]={"<=$(=Date(Addyears(Today(),-0),'DD.MM.YYYY'))"}, YearMonth= >} Sales)


As you see, there are several possibilities to check, if you can't make it work, then please post a sample QVF.

Not applicable

Re: Sum where the date is lower than today's date minus 1 year

Hello swuehl

Lot of thanks for the interest.

Actually it is not a chart but a KPI indicator, so there is not any dimension being used in it. This is the KPI with the Sum(sales) expression indicated on it:

Sum where date is lower screenshot 3.jpg


The format of [Date field] is DD/MM/YYYY H:MMSmiley FrustratedS, being H:MMSmiley FrustratedS always 0:00:00:

Sum where date is lower screenshot 2.jpg

This format is not intended, QS takes the data from our ERP system, but in there date is just DD/MM/YYYY, it gets transformed when it arrives to Qlik Sense. So far this date format hadn't caused any trouble, but I guess it is actually causing some mess in here.

If by 'have anything selected' you mean if I have any filter applied, the answer is no.

Thanks and regards

Not applicable

Re: Sum where the date is lower than today's date minus 1 year

Hello Gysbert

I have just prepared an app with Date, Month and Sales fields extracted from our ERP where you can see the situation.

There are two KPIs one with Sum(sales) and the other with Sum({<[Date]={"<=$(=Addyears(Today(),-0))"}>} Sales)

Thanks and regards

Not applicable

Re: Sum where the date is lower than today's date minus 1 year

Hello Sunny T

That was exactly the problem. I had to indicate my date field format in the expression to make it work. This is how the function looks like now:

Sum({<[Date]={"<=$(=Date(Addyears(Today(),-1),'DD/MM/YYYY H:MMSmiley FrustratedS'))"}>} Sales)

Now it is calculating the sum of the sales that were posted before 14/04/2015, which is great.

Now, I have one last doubt: is it better to reformat date field to 'DD/MM/YYY' or it doesn't really matter?

Thanks and regards