Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
wuming79
Creator
Creator

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 (1)
5 Replies
Anil_Babu_Samineni

You don't like to create your timestamp as field?
Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
wuming79
Creator
Creator
Author

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
Creator
Creator
Author

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.

Anil_Babu_Samineni

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)
Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
wuming79
Creator
Creator
Author

The Only method gave invalid dimension.

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

timestamp.PNG