topic Re: calculate gap between max date and min date in one scope in QlikView

calculate gap between max date and min date in one scope

wenkew186 — Mon, 06 Feb 2017 08:26:12 GMT

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?

Re: calculate gap between max date and min date in one scope

tresB — Mon, 06 Feb 2017 08:53:18 GMT

Try:

FirstSortedValue(Aggr(Sum(Sales), Week,Date),-Date)

FirstSortedValue(Aggr(Sum(Sales), Week,Date),Date)

Re: calculate gap between max date and min date in one scope

wenkew186 — Mon, 06 Feb 2017 09:49:27 GMT

Hi Tressco,

look my expression, but it is not works for my real data,no any data display. is there any condition for this fucnction (FirstSortedValue)?

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

Re: calculate gap between max date and min date in one scope

tresB — Mon, 06 Feb 2017 10:01:45 GMT

Do you have two different dates - [SNP Process Date] and [SNP Date] ? I guess you have to use one date field in both the places in the expression.

Re: calculate gap between max date and min date in one scope

uacg0009 — Mon, 06 Feb 2017 10:06:08 GMT

Hi Kevin,

I think Tresesco is so good, and if you can make sure the Date is a distinct value, no any repeat date in one week.

Then I think the formula also can be :

FirstSortedValue(Sales,-Date)

FirstSortedValue(Sales,Date)

Thanks.

Re: calculate gap between max date and min date in one scope

vinieme12 — Mon, 06 Feb 2017 10:14:21 GMT

why not [SNP Process Date], is it different?

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

Re: calculate gap between max date and min date in one scope

wenkew186 — Tue, 07 Feb 2017 02:45:13 GMT

Pujari,

thanks, it should one name.

Re: calculate gap between max date and min date in one scope

wenkew186 — Tue, 07 Feb 2017 02:46:51 GMT

Tresesco,

thank you, it's should one name, just write wrong.

thanks,

Re: calculate gap between max date and min date in one scope

wenkew186 — Tue, 07 Feb 2017 02:56:30 GMT

Aiolos ,(changjun?)

hope you are well!

what's means no any repeat date? the template is simple, but in my real data there have many dimension, so it means many row base on dimension in one date.

thanks,

Kevin.

Re: calculate gap between max date and min date in one scope

wenkew186 — Tue, 07 Feb 2017 03:32:20 GMT

Hi Tresesco,

looks the issue is date not distinct, like Aiolos said. but my real data have many dimension. so one date has many row data. I have update a new template. can you help check again?

thanks.

Re: calculate gap between max date and min date in one scope

uacg0009 — Tue, 07 Feb 2017 03:53:46 GMT

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

Re: calculate gap between max date and min date in one scope

vinieme12 — Tue, 07 Feb 2017 05:13:33 GMT

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

Re: calculate gap between max date and min date in one scope

wenkew186 — Tue, 07 Feb 2017 06:20:42 GMT

Thank you Tresesco!

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

thanks,

Kevin.