problematical FirstSortedValue

[#1]

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.

[sunny_talwar]

How about this:

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

[#3]

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

[sunny_talwar]

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".

[#5]

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

[sunny_talwar]

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

[#7]

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. 

[#8]

15:50 I from lithuania

[sunny_talwar · Accepted solution]

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 '|');

[swuehl]

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.