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

[#1]

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

[Gysbert_Wassenaar]

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

[#3]

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

[swuehl]

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

[Gysbert_Wassenaar]

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

[sunny_talwar · Accepted solution]

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?

[swuehl]

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.

[#8]

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:

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

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

[#9]

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

[#10]

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:MM:SS'))"}>} 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