Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

arsallee3
New Contributor III

Sum Value based upon date time

Hello,

I am working to get a sum based upon a date time field. I would like to sum value of field it day is equal to current date or previous date.

this is the formula currently have and just get the sum for all days, sure have some sytanx off slightly.

Sum({$<[Date(TicketDate)]={[today()]}>}BblsQuantity)

Thanks

Andy

1 Solution

Accepted Solutions
MVP
MVP

Re: Sum Value based upon date time

If TicketDate is a timestamp with date and time components, split the components during load like:

LOAD ...

     Date(Floor(TicketDate)) As TicketDate,

     Time(Frac(TicketDate)) As TicketTime,

     ...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
5 Replies
balrajahlawat
Esteemed Contributor

Re: Sum Value based upon date time

Try this one:

I am assuming TicketDate is in 'DD/MMM/YYYY' format

=sum({1<TicketDate={'=$(=date(today(),'DD/MMM/YYYY'))'>}BblsQuantity)

MVP
MVP

Re: Sum Value based upon date time

Its simpler if you have everything is in the default date format:

=Sum({<TicketDate = {'$=(Today())'}>} BblsQuantity)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
MVP
MVP

Re: Sum Value based upon date time

If TicketDate is a timestamp with date and time components, split the components during load like:

LOAD ...

     Date(Floor(TicketDate)) As TicketDate,

     Time(Frac(TicketDate)) As TicketTime,

     ...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Sum Value based upon date time

if TicketDate format is in proper date format you can do this

sum({<TicketDate={">=$(=max(TicketDate)-1))"}>}BblsQuantity)

arsallee3
New Contributor III

Re: Sum Value based upon date time

I did the split and combination of the following string worked for me.

Sum({<TicketDateNum={'$(=date(today()-1))'}>}BblsQuantity)

I appreciate the assistance.