Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 '|');
How about this:
=Date(FirstSortedValue(DISTINCT {<Date = {">$(=TimeStamp(LocalTime(), 'M/D/YYYY h:mm:ss'))"}>} Date, Aggr(Count(UnitSales), Date)))
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
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".
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
I guess I am in a different timezone, what is your time right now?
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.
15:50 I from lithuania
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 '|');
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.