topic Re: problematical FirstSortedValue in QlikView

problematical FirstSortedValue

Tue, 20 Dec 2016 09:56:21 GMT

Hello Qlik professors,

Need advices , also it would be good if someone explained my mistakes.

I have a problem of getting first ( lowest ) Date where is minimum count UnitSales and which is higher than today's date.

For example LocalTime() = 12/20/2016 12:00:00

Then i need too get 12/22/2016, because at day 22 count(UnitSales) = 0 and because 12/22/2016 > 12/20/2016 12:00:00 but

12/22/2016 < 12/23/2016 01:00:00 .

My data looks like that:

ProductData:

LOAD * inline [

Customer|Product|UnitSales|Date

Astrida|AA||12/19/2016 23:00:00

Astrida|AA|12|12/20/2016 10:00:00

Astrida|BB||12/20/2016 10:30:00

Betacab|BB|5|12/20/2016 12:15:00

Betacab|CC|1|12/20/2016 12:16:00

Betacab|DD|1|12/20/2016 12:30:00

Canutility|AA|8|12/20/2016 12:45:00

Canutility|CC|1|12/20/2016 13:00:00

Astrida|CC||12/20/2016 14:00:00

Astrida|AA|10|12/20/2016 15:00:00

Astrida|CC||12/21/2016 11:00:00

Betacab|CC|5|12/21/2016 17:00:00

Betacab|CC||12/22/2016 20:00:00

Betacab|CC||12/23/2016 01:00:00

] (delimiter is '|');

Feels like i have tried everything, this is the closet:

Date(

firstsortedvalue (

distinct {< Date = {'>$(=LocalTime())'} >} Date,

aggr(

count(UnitSales)

, Date

)

I hope there is solution

Thank you for your time.

Re: problematical FirstSortedValue

sunny_talwar — Tue, 20 Dec 2016 13:00:50 GMT

How about this:

=Date(FirstSortedValue(DISTINCT {<Date = {">$(=TimeStamp(LocalTime(), 'M/D/YYYY h:mm:ss'))"}>} Date, Aggr(Count(UnitSales), Date)))

Re: problematical FirstSortedValue

Tue, 20 Dec 2016 13:41:18 GMT

it returns 12/21/2016 and i need 12/22/2016.

if we count UnitSales then Day 20 will have "7+2*0", Day 21 "1+0", Day 22 "0", Day 23 "0".
I need date 12/22/2016 because it have lowest count and and lowest date.

Best Regards

Re: problematical FirstSortedValue

sunny_talwar — Tue, 20 Dec 2016 13:45:55 GMT

Not sure I am understanding the logic here

if we count UnitSales then Day 20 will have "7+2*0", Day 21 "1+0", Day 22 "0", Day 23 "0".

Re: problematical FirstSortedValue

Tue, 20 Dec 2016 13:46:38 GMT

if change to =Timestamp(FirstSortedValue(DISTINCT {<Date = {">$(=TimeStamp(LocalTime(), 'M/D/YYYY h:mm:ss'))"}>} Date, Aggr(Count(UnitSales), Date))) then it returns 12/21/2016 11:00:00 instead of 12/22/2016 20:00:00

Re: problematical FirstSortedValue

sunny_talwar — Tue, 20 Dec 2016 13:48:37 GMT

I guess I am in a different timezone, what is your time right now?

Re: problematical FirstSortedValue

Tue, 20 Dec 2016 13:50:33 GMT

Trying my best.

I mean if we count UnitSales per day we at day 20 we will get 7 because there 7 values and 2 null() values.

Re: problematical FirstSortedValue

Tue, 20 Dec 2016 13:51:14 GMT

15:50 I from lithuania

Re: problematical FirstSortedValue

sunny_talwar — Tue, 20 Dec 2016 14:01:49 GMT

Try this:

=Date(FirstSortedValue({<TimeStamp = {">$(=TimeStamp(LocalTime(), 'M/D/YYYY h:mm:ss'))"}>} Date, Aggr(Sum({<TimeStamp = {">$(=TimeStamp(LocalTime(), 'M/D/YYYY h:mm:ss'))"}>}UnitSales) + Date/1E4, Date)))

Where I modified the script a little

ProductData:

LOAD Customer,

Product,

UnitSales,

Date as TimeStamp,

Date(Floor(Date)) as Date;

LOAD * inline [

Customer|Product|UnitSales|Date

Astrida|AA||12/19/2016 23:00:00

Astrida|AA|12|12/20/2016 10:00:00

Astrida|BB||12/20/2016 10:30:00

Betacab|BB|5|12/20/2016 12:15:00

Betacab|CC|1|12/20/2016 12:16:00

Betacab|DD|1|12/20/2016 12:30:00

Canutility|AA|8|12/20/2016 12:45:00

Canutility|CC|1|12/20/2016 13:00:00

Astrida|CC||12/20/2016 14:00:00

Astrida|AA|10|12/20/2016 15:00:00

Astrida|CC||12/21/2016 11:00:00

Betacab|CC|5|12/21/2016 17:00:00

Betacab|CC||12/22/2016 20:00:00

Betacab|CC||12/23/2016 01:00:00

] (delimiter is '|');

Re: problematical FirstSortedValue

swuehl — Tue, 20 Dec 2016 14:09:11 GMT

Sunny, take care to add small amounts proportional to date as weight, that are smaller than the UnitSales increments you are aggregating. In this case, I would use at least Date/1E5.

Re: problematical FirstSortedValue

sunny_talwar — Tue, 20 Dec 2016 14:12:06 GMT

Yes you are right, I was going to do 1E10, but somehow ended with just 1E4

Re: problematical FirstSortedValue

Tue, 20 Dec 2016 14:15:36 GMT

Many many Thanks, its correct answer.

But could you please explain this part + Date/1E4, can't figure out what its doing.

Best Regards.

Re: problematical FirstSortedValue

sunny_talwar — Tue, 20 Dec 2016 14:18:04 GMT

Since there were two dates to choose from 12/21 and 12/22, I added back the date to give them different sorting weights. 12/21 in its numeric form is divided by 1E10 (1E4 might not be a good idea) to make its sort weight smaller than the sort weight of the Sum(UnitSales) which is the first thing we want to worry about.

Re: problematical FirstSortedValue

Tue, 20 Dec 2016 14:25:13 GMT

Thanks