Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have tried to solve what I thought would be a simple task for a while now and I must be missing something.
I have defined two variables:
beforeDate = 2018-10-30
lastDate = MAX({$<dateCol={"<=$(=beforeDate)"}>}dateCol)
In a chart i have no trouble using "=$(lastDate)" in order to get the most current date before "beforeDate".
But when I want to use it in a Set analysis i get into trouble.
This is my current expression:
Sum({$<dateCol={"=$(=lastDate)"}>}costCol)
What am I missing?
This worked perfectly!!!
Thank you
Now I just need to read up on the aggr function since i'm not comfterble with the use of that just yet
Patrick,
Glad it was helpful. I normally end up re-reading the aggr definition every time I use it .
Cheers,
Chris.
Hi Chris,
Sorry for opening this thread again.
I thought that it worked out turns out it didn't and when I rebuild your application I understand why as Stefan pointed it out for me. FirstSorted value is not able to sum two values on the same date so when I add a second line with the same date I get 0. Is there any other way to get around this?
I need to get the result 17 for Account A if using this data.
Data:
LOAD * INLINE [
Account, dateCol, costCol
A, 2018-10-31, 10
A, 2018-09-30, 8
A, 2018-09-30, 9
A, 2018-08-31, 7
B, 2018-10-31, 12
B, 2018-08-31, 11
];
Patrick,
No worries. I am not sure why the following works (or seems to), but give it a go;
FirstSortedValue({$<dateCol={"<$(beforeDate)"}>} distinct aggr(sum({$<dateCol={"<$(beforeDate)"}>}costCol), Account, dateCol),-dateCol)
I would have assumed the distinct would have applied to the value (the aggregation) and therefor be irrelevant (it is aggregating, after all), but seems like it also has an impact on the sort weight (-dateCol).
Cheers,
Chris.