Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Paknanarn23
Creator II
Creator II

How do I sum this condition?

I want to sum AmpH from Date Out starting at 05:00:00 AM of the first day to 05:00:00 AM of the next day.

Paknanarn23_0-1675397660416.png

 

Labels (4)
1 Solution

Accepted Solutions
avkeep01
Partner - Specialist
Partner - Specialist

Hi @Paknanarn23 , 

You could use this to get 1 date for all values between 05:00:00 this day and 05:00:00 the next day. If the date is later then 12:00:00 PM then it will substract 1 day for the timestamps before 05:00:00. 

TIMESTAMP(DATE(IF(TIME(DATE_OUT-DAYSTART(DATE_OUT))>=TIME('05:00:00') ,DAYSTART(DATE_OUT),DAYSTART(DATE_OUT)-1),'DD-MM-YYYY')+TIME('05:00:00'),'M/DD/YYYY h:mm:ss TT') AS GroupByDate

This is the result: 

avkeep01_0-1675415136797.png

 

 

View solution in original post

5 Replies
tresesco
MVP
MVP

Can you explain how you are getting these numbers and how do you want to sum them?

Paknanarn23
Creator II
Creator II
Author

Hi  ,

I got this sum from Database, it's a value fetched from Database at all.

where I want to sum the Amph values ​​from Date Out 5:00 AM of the first selected date to 5:00 AM of the last selected date.

tresesco
MVP
MVP

I am still not clear about your 5 a.m. part. Could you explain with a selection case?

Paknanarn23
Creator II
Creator II
Author

It's not a choice. But it is a condition for sum(AmpH) that if the time is 5am, sum(ampH) will come out in each Cu Tank

For example, if CU TANK = 26, you will get this value.

The time will start from 6am on January 30, 2023 until 4am on January 31, 2023

2023_02_03_11_55_21_Microsoft_Excel_AmpH.png

 

 

avkeep01
Partner - Specialist
Partner - Specialist

Hi @Paknanarn23 , 

You could use this to get 1 date for all values between 05:00:00 this day and 05:00:00 the next day. If the date is later then 12:00:00 PM then it will substract 1 day for the timestamps before 05:00:00. 

TIMESTAMP(DATE(IF(TIME(DATE_OUT-DAYSTART(DATE_OUT))>=TIME('05:00:00') ,DAYSTART(DATE_OUT),DAYSTART(DATE_OUT)-1),'DD-MM-YYYY')+TIME('05:00:00'),'M/DD/YYYY h:mm:ss TT') AS GroupByDate

This is the result: 

avkeep01_0-1675415136797.png