Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
catalyst_75
Contributor II
Contributor II

Sum a range of billable time between 2 dates in expression

I have a table of billable entries. They have a date field of when the activity happened and I have that loaded in the script with a date format of 'mm/dd/yy'. There can be one or many records on each day that I need this table to sum for me according to each claimant. For each claimant, there is one record and is has a Claim Date.

What I am trying to do with this table is break apart some of the billing listing into date periods.

First period: Claim Date + 4 days

Second period : Claim Date+5 through Claim Date+34

, etc. as to have sort of billing periods or time.

Here is my expression and it appears and reads correct to me, but I get zeros in my column unless I have the Activity Listing Date = Claim Date. But that is not letting me do what I want. What I am missing? Or what I am doing wrong as to be able to grab all records between 2 set date in an expression.

=Sum({$<[Activity Listing Date]={">=[Claim Date]<([Claim Date] + 4)"}>}[Activity Listing Log Time])

Add note" each of those periods will be in a different expression column.

Thank for any help

2 Replies
Gysbert_Wassenaar

I think you're not aware that the set is calculated at the chart level, not at the row level. See here for an explanation: set_analysis_intra-record.qvw

Perhaps you can create a flag field in the load statement in the script to mark the records that meet your range criteria. If not, you may have to resort to using if-statements.


talk is cheap, supply exceeds demand
catalyst_75
Contributor II
Contributor II
Author

Well, this will work and give me that first days work of summed totals.

=Sum({$<[Activity Listing Date]={"=[Claim Date]"}>}[Activity Listing Log Time])

I tried to create a second column expression that would do a second day base off the Claim Date

=Sum({$<[Activity Listing Date]={"=(Date([Claim Date])+1)"}>}[Activity Listing Log Time])

And this displays the same summed amount as what appears in that first column.

Does it have more to do with my date comparisons or the actual expression on trying to Sum them?