13 Replies Latest reply: Dec 20, 2016 9:25 AM by Denis V RSS

    problematical FirstSortedValue

    Denis V

      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.