topic Re: Sum a range of billable time between 2 dates in expression in QlikView

Sum a range of billable time between 2 dates in expression

catalyst_75 — Fri, 19 Dec 2014 18:00:11 GMT

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

Re: Sum a range of billable time between 2 dates in expression

Gysbert_Wassenaar — Fri, 19 Dec 2014 18:03:27 GMT

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.

Re: Sum a range of billable time between 2 dates in expression

catalyst_75 — Fri, 19 Dec 2014 18:18:16 GMT

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?