Sum last 12 Months

[Georg022]

Hi, 

I need a sum for the last 12 calender-months. 

f.e. we have 11/2021, so I need a sum for all sales between 12/2020 and 11/2021.

The normal sum I have is:

sum(SAP_Nettowert)

SAP_Nettowert is my sales-number

The fields for my date colums (in the import-script) are:

SAP_Rechnungsdatum_Tag
SAP_Rechnungsdatum_Monat
SAP_Rechnungsdatum_Jahr

I already have something like this to show the sales-numbers for the actual month f.e. 2020. But I can't figure out how to chance the formula so I get a sum for the LAST 12 Months, like described above.

sum({<SAP_Rechnungsdatum_Monat = {$(=maxstring(SAP_Rechnungsdatum_Monat))},SAP_Rechnungsdatum_Jahr={$(=max(SAP_Rechnungsdatum_Jahr)-2)}>}SAP_Nettowert)

Thank you very much

Georg

[Vegar]

When assuming that Tag is a date field, then you can do this:

sum({<SAP_Rechnungsdatum_Monat,SAP_Rechnungsdatum_Jahr, SAP_Rechnungsdatum_Tag={">=$(=addmonths(max(SAP_Rechnungsdatum_Tag),-12) "}>}SAP_Nettowert)

[Georg022]

Thank you 🙂

It didn´t work, but I think I described it wrong. I import like this:

LOAD ,
SAP_Number,
SAP_Rechnungsdatum,
SAP_Rechnungsdatum_Tag,
SAP_Rechnungsdatum_Monat,
SAP_Rechnungsdatum_Jahr,
SAP_Nettowert,......

The data in Excel looks like this:

SAP_Rechnungsdatum	SAP_Rechnungsdatum_Tag	SAP_Rechnungsdatum_Monat	SAP_Rechnungsdatum_Jahr
13.04.2021	13	4	2021
13.04.2021	13	4	2021
13.04.2021	13	4	2021

I know I could extract the day, month and year in Qlikview but this is how I got the data 🙂

In the settings of the script I have:

SET DateFormat='DD.MM.YYYY';

So Qlikview should recognice the "SAP_Rechnungsdatum"-Field as date.

Can you see why your expressions does not work in my case?

Thank you so much.

Georg

PS: I uploaded a testfile with the data... maybe it is easier this way 🙂

[Vegar]

If Tag is Monday to Saturday, then it won't work comparing it to a date. Try this instead. (I also fixed a missing parenthesis error from  my earlier statement)

sum({<SAP_Rechnungsdatum_Monat, SAP_Rechnungsdatum_Jahr, SAP_Rechnungsdatum_Tag,

SAP_Rechnungsdatum={">=$(=addmonths(max(SAP_Rechnungsdatum),-12)) "} >}SAP_Nettowert)

[Georg022]

Tag is the day of the month (1-31). I am so sorry, but it doesn't work... Maybe the problem is the field itself? SAP_Rechnungsdatum is imported as normal field, does Qlikview recognize it as date-field?

[Vegar]

I am travelling  without access to a computer so I can't check your attached files, but you should be able to ensure that your date field will be interpreted as a date using the date#() function.

Try to load the data like this:

LOAD ,
SAP_Number,
date#(SAP_Rechnungsdatum, 'DD.MM.YYYY') as SAP_Rechnungsdatum
SAP_Rechnungsdatum_Tag,
SAP_Rechnungsdatum_Monat,
SAP_Rechnungsdatum_Jahr, ...