SUM only values which date is the newest date

[#1]

Hi Community,

i have xls sheets from different weeks which i import to my qlikview dashboard.

i am summing up different target numbers to one "target of the week". My problem is that i am only interested in the target of the latest week. But sum([target]) sums all targets from all weeks to one very unrealistic (big) value.

i think a formula like this should work to get the current target from the latest week but it does not 😞

=SUM({< DateofFile = {"==MAX(DateofFile)"} >}[target])

This one to find out the latest date works:

=max(DateofFile)

so i am very confused...

Can you provide help?

[#2]

it has to be like that ;

sum({$<DateofFile = {"$(=max(DateofFile))"}>} [target])

[#3]

thank you for this answer but there is still the same problem.

let me give you an example:

week 1: SUM(target) is 1000 + 1000 + 1000 = 3000

week2: SUM(target) is 1000 + 1000 + 1200 = 3200

So 3200 is the value that i want to have (weekly target from latest date)

your and my formula makes SUM to 3000 + 3200 = 6200

why?

thank you for your help and best regards

[#4]

So we have to set week number . If you dont have create on the script with "week()"

sum({$<WeekofFile = {"$(=max(WeekofFile))"}>} [target])

Best regards

[#5]

like this? LET WeekofFile = week(DateofFile);

[#6]

on your load statement ;

target:
Load *,week(DateofFile) as WeekofFile;
Load * From YourData

[#7]

ok i tried it with this week formula. The formula delivers the same wrong number than the one with DateofFile.

I tested with a pivot table so see what happens at each week. I think now i got the problem with that formula:

If week is NOT max week there is SUM = 0.

But if week IS max(week) then the formulas sums up all values and not only those from the latest file (max week file).

Do you understand what i mean?

Really thank you for your help!