calculate gap between max date and min date in one scope

[wenkew186]

hi folks,

In my data, there have some date in one week.

I want get the gap between the max date with min date in same week . look the template. it means max date data minus min date data in this week by week dimension. how to write the expression?

[uacg0009]

Hi Kevin,

Glad to see you, I didn't think that is you.

Well, I think the Tresesco using the AGGR in FirstSortedValue is just for group by the Week,Date when calculating the sum Sales.

So I think if your date is not like

Week1 2016-07-01 10

Week1 2016-07-01 30

Week1 2016-07-02 20

Two same date in one week, so I think you don't need to use the AGGR.

But like you said, your data is more complex, you have many dimensions.

So I think you can try to not use the AGGR, if not correct(I think that's because of the repeat date in dimension), then use AGGR, even though it will make the table a little slow.

Thanks.

Aiolos

[vinieme12]

Try below

FirstSortedValue(Aggr(Sum([Secured Amount]), Week,[SNP Process Date]),-Aggr ([SNP Process Date],Week,[SNP Process Date]))
-
FirstSortedValue(Aggr(Sum([Secured Amount]), Week,[SNP Process Date]),Aggr ([SNP Process Date],Week,[SNP Process Date]))

[wenkew186]

Thank you Tresesco!

it works when add 'distinct'. that's really convenient than create in script.

thanks,

Kevin.