topic Re: SUM with Set Expression in QlikView

SUM with Set Expression

Anonymous — Mon, 23 Jan 2017 16:36:59 GMT

In a chart expression, I want to sum a field, but only if the field is greater than 10. In the record set I'm working with, there are four values - one is less than 10, and the rest are greater than 10.

The following expression definition results in a sum of 0 (which is not correct):

Sum({$< [PAWorkRVU]= {'>=10.0'}>} [PAWorkRVU])

The following expression definition results in a sum of all rows regardless of value (which is not correct).

Sum({$< [PAWorkRVU]= {'<=10.0'}>} [PAWorkRVU])

What am I doing wrong?

Re: SUM with Set Expression

sunny_talwar — Mon, 23 Jan 2017 16:43:10 GMT

Can you share a list box object snapshot for the field PAWorkRVU?

Re: SUM with Set Expression

Anonymous — Mon, 23 Jan 2017 17:04:56 GMT

Well, all of the values in the list are less than 10. It seems that my requirements may not have been sufficiently defined.

The chart in question is a pivot table that pivots on a date field, so lets assume that the values need to be summed for a current date before being compared to the control value (10). How would I do that?

Re: SUM with Set Expression

sunny_talwar — Mon, 23 Jan 2017 17:10:00 GMT

May be try this:

If(Sum(PAWorkRVU) >= 10, Sum(PAWorkRVU))

Re: SUM with Set Expression

Anonymous — Mon, 23 Jan 2017 17:14:16 GMT

That returns all rows. There is one date that has less a value than 10, and therefore should not be included in the result.

Re: SUM with Set Expression

sunny_talwar — Mon, 23 Jan 2017 17:15:12 GMT

Don't really know unless you can share a sample or may be an image?

Re: SUM with Set Expression

Anonymous — Mon, 23 Jan 2017 17:37:58 GMT

It turns out that my requirements were WILDLY misstated.

I need to count the number of dates where the monthly sum of PAWorkRVU was > 10. Something like this:

SUM( IF( SUM([Provider Aggregate Work RVU]) >= 10, 1, 0) )

Re: SUM with Set Expression

krishnacbe — Mon, 23 Jan 2017 17:47:05 GMT

Hi,

Try below expression,

Count({$< [PAWorkRVU]= {'>=10.0'}>} [Month])

Count({$< [PAWorkRVU]= {'>=10.0'}>} distinct [Month])

Re: SUM with Set Expression

sunny_talwar — Mon, 23 Jan 2017 18:00:09 GMT

May be this:

Sum(Aggr(If(Sum(PAWorkRVU) > 10, 1, 0), Month))

Re: SUM with Set Expression

Anonymous — Mon, 23 Jan 2017 18:03:26 GMT

That gives me 0, which is not correct. It has to be the sum of PAWorkRVU for a given date. I'm having trouble using functions in the set analysis expression. I think I need something like this (the following expression is invalid, but I think this is the way it needs to evaluate):

COUNT({$< SUM([PAWorkRVU])= {'>=10'} >} [CalendarDate])

Re: SUM with Set Expression

Anonymous — Mon, 23 Jan 2017 18:04:25 GMT

That was it.

Re: SUM with Set Expression

sunny_talwar — Tue, 24 Jan 2017 00:31:48 GMT

If the above worked, you can use set analysis also

Count(DISTINCT {<Month = {"=Sum(PAWorkRVU) > 10"}>} Month)

Re: SUM with Set Expression

xyz1 — Sun, 14 Apr 2019 11:11:09 GMT

Re: SUM with Set Expression

Anonymous — Tue, 24 Jan 2017 16:17:50 GMT

Well, I think it still needs tweaking. This is what I have:

Count(DISTINCT {<CalendarDate = {"=Sum([PAWorkRVU]) > 10"}>} CalendarDate)

When I view an unfiltered list, the value is incorrect. When I select just one row in the table, the value is correct. The data looks like this:

PROVIDER     CalendarDate     PAWorkRVU
Person1           01/01/2017         6.1
Person1          01/01/2017        10.5
Person1          02/01/2017        6.1
Person1          02/01/2017        10.5
Person1          03/01/2017        6.1
Person1          03/01/2017        10.5
Person1          04/01/2017        6.1 <<----- Less than 10 for 04/01/2017
Person2          01/01/2017        6.1
Person2          01/01/2017        10.5
Person2          02/01/2017        6.1
Person2          02/01/2017        10.5
Person2          03/01/2017        6.1
Person2          03/01/2017        10.5
Person2          03/01/2017        6.1
Person2          03/01/2017        10.5

The table I'm working with is supposed to show the following:

PROVIDER     PAWorkRVU     QualifyingMonths
Person1          85.9                   3 <<--- only three months meet the criteria
Person2          66.4                   4 <<--- four months meet the criteria

In this table PAWorkRVU shows the sum of all rows for the given PROVIDER, and the QualifyingMonths is the number of months where the total PAWorkRVU for a given month exceeds the value of 10.

When no providers are selected, Person1 shows 4 months that meet the criteria (which is obviously wrong). However, when I specifically select Person1 in the table, the value correctly shows 3.

Re: SUM with Set Expression

sunny_talwar — Tue, 24 Jan 2017 16:36:47 GMT

I would then use the Aggr() function....

Sum(Aggr(If(Sum(PAWorkRVU) > 10, 1, 0), CalendarDate, PROVIDER))

or if you can create a new field in the script which combines CalendarDate and Provider like this

LOAD CalendarDate,

PROVIDER,

AutoNumber(CalendarDate&PROVIDER) as NewField,

....

FROM....

then you can use set analysis like this

Count(DISTINCT {<NewField = {"=Sum([PAWorkRVU]) > 10"}>} CalendarDate)

Re: SUM with Set Expression

Anonymous — Tue, 24 Jan 2017 17:08:38 GMT

That appears to have done the trick. Many thanks.