Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
wuming79
Contributor

Bar Chart to show last 7 days with epoche converted time

Hi,

I have my timestamp converted with expression as below:

=Timestamp(Timestamp('1970-01-01 00:00:00.000') + (epochtime+ 28800)/60/60/24)

When I plot my bar chart, I have too many days. I like to show only last 7 days. 

I found a reference to do this in epoch and YYYY/MM/DD format at https://community.qlik.com/t5/New-to-Qlik-Sense/how-to-show-data-of-last-7-days-in-bar-chart/td-p/86...

but how do I put my expression into the expression given in the link above?

Labels (2)
5 Replies

Re: Bar Chart to show last 7 days with epoche converted time

You don't like to create your timestamp as field?
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
wuming79
Contributor

Re: Bar Chart to show last 7 days with epoche converted time

I tried creating a new field using the following syntax during data loading.

timestamp((timestamp/ 86400 + 25569 + 28800),'DD/MM/YYYY h:mm:ss[.fff] TT') as OV_stati_datetime,

But I can't shift my time properly. 

The blue bars are from Friday data and the red Bars are from Saturday Data. How do I push the Friday start time from 16/11/2018 1555hr to 17/11/2018 0000hr?

timestamp.PNG

wuming79
Contributor

Re: Bar Chart to show last 7 days with epoche converted time

I managed to create a field 

timestamp((timestamp/86400 + 25569 + 8/24),'DD/MM/YYYY h:mm:ss[.fff] TT') as  datetime,

My datetime is in the format,  DD/MM/YYYY H:MM:SS AM/PM.

I used the following example from the link above, replacing "Unit*Price" with "datetime".

 

Sum({$<OrderDate={">=$(=Date(Today()-7))<=$(=Date(Today()-1))"}>}Units*Price) (when in date format e.g.  YYYY/MM/DD )

with 

Sum({$<OrderDate={">=$(=Date(Today()-7))<=$(=Date(Today()-1))"}>}datetime) 

 

I got invalid dimension.

Re: Bar Chart to show last 7 days with epoche converted time

FYI - datetime never aggregate with Sum as this works when you have Number format. Try this way?

Only({$<OrderDate={">=$(=Date(Today()-7))<=$(=Date(Today()-1))"}>}datetime) 

 Or

If(OrderDate>=Date(Today()-7) and OrderDate<=Date(Today()-1), datetime)
Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
wuming79
Contributor

Re: Bar Chart to show last 7 days with epoche converted time

The Only method gave invalid dimension.

The If method shows only 2 bars with the expression field box outlined  red.

timestamp.PNG