topic Re: RangeSum or Multiple Sums in Set Analysis in QlikView

RangeSum or Multiple Sums in Set Analysis

Thu, 11 Feb 2016 21:20:08 GMT

Hi,

I want to sum 2 different amount values in set analysis. However, the 2 amount values are dependent on selections of 2 different fields available in list boxes. I prefer not to use any IF conditions and rather use set analysis. Is this possible?

NUM((RangeSum(If(GetSelectedCount(MonthCurrent) and [Category]='Credit',sum(SalesCurrent * -1)),If(GetSelectedCount(MonthProjected) and [Category]='Credit', Sum(SalesProjected * -1))),'$#,##0'))

Re: RangeSum or Multiple Sums in Set Analysis

sunny_talwar — Thu, 11 Feb 2016 21:39:07 GMT

Have not tested this, but may be try this:

RangeSum(

Sum({<MonthCurrent = {"=GetSelectedCount(MonthCurrent) > 0"}, Category = {'Credit'}>} (SalesCurrent * -1)),

Sum({<MonthProjected = {"=GetSelectedCount(MonthProjected) > 0"}, Category = {'Credit'}>} (SalesProjected * -1)))

Re: RangeSum or Multiple Sums in Set Analysis

Thu, 11 Feb 2016 22:42:55 GMT

Thanks Sunny! This is coming up correct now. Can we show the sum even if no Months are selected ? i.e if no months are selected from MonthCurrent or MonthProjected, it should show the total sum of MonthCurrent and MonthProjected together.

Re: RangeSum or Multiple Sums in Set Analysis

swuehl — Thu, 11 Feb 2016 22:45:36 GMT

I think I know where you are coming from, but could you detail

- why you don't want to use the if() statement here?

Since it's not evaluated per record, there is no big performance impact here.

- How Category field is related to your fact / sales fields?

For checking the selection in month fields, I could imagine either a straight value assignment using an GetfieldSelections() dollar sign expansion in a field modifier (like MonthCurrent = {$(=GetFieldSelections(MonthCurrent,',',12))} ) or shorter a field name field modifier ( MonthCurrent = MonthCurrentSelector ) using a data island or alternate state foryour selections (a solution which you don't like, if I remember correctly).

From a maintenance point of view, I don't think the set analysis expression will be easier to understand than the above posted expression (given that I think I know some things about your model).

Re: RangeSum or Multiple Sums in Set Analysis

swuehl — Fri, 12 Feb 2016 07:47:09 GMT

That should be default QV behaviour. Just remove the field modifier for months field or the if() statement that checks selections in months fields.

Re: RangeSum or Multiple Sums in Set Analysis

Anonymous — Fri, 12 Feb 2016 07:53:05 GMT

According to My sum({<Field name={'Value'}>}Sales)+ sum({<Field name={'Value'}>}Sales)

try it in this way