topic Re: set analysis for formula like sum(a-b) in QlikView

set analysis for formula like sum(a-b)

Thu, 30 Aug 2012 08:19:39 GMT

Hi,

I'm trying to create an expression with SET.

It is easy to do this...

sum ( {$<....>} a) - sum ( {$<....>} b)

BUT... how do i do it when i want it to be SUM (a - b), applying the same set analyis for both a and b?

Thanks,

Re: set analysis for formula like sum(a-b)

whiteline — Thu, 30 Aug 2012 08:26:41 GMT

sum ( {$<....>} a-b)

sum ( {$<....>} RangeSum(a, -b))

Re: set analysis for formula like sum(a-b)

Thu, 30 Aug 2012 08:32:32 GMT

i can't do that. because the SET criteria for a and b are different....

Re: set analysis for formula like sum(a-b)

whiteline — Thu, 30 Aug 2012 09:03:09 GMT

I thought you said its the same.

Ok, use Only({$...} a) - Only({$...} b)

Re: set analysis for formula like sum(a-b)

Thu, 30 Aug 2012 09:17:09 GMT

yes.. my bad.. typed too quick..

so, perfect, it works...

now, as i'm doing this in pivot table...

the subtotal wont work anymore.. it disappear...

any tips?

Re: set analysis for formula like sum(a-b)

Thu, 30 Aug 2012 09:21:02 GMT

and also.. how does ONLY() works?

i tried to look up in the document, it's not very helpful...

the reason why the total is not working.. because...

instead of doin sum (only(a) - only(b)),

i remove the sum, otherwise the figures wont show...

thats why i asked how does only() works...

it's weird...

Re: set analysis for formula like sum(a-b)

whiteline — Thu, 30 Aug 2012 09:29:30 GMT

All answers depends on your data structure. You can reduce your file and post it or try to give more info.

About Only():

Originally this function checks if your field values are all the same and return this value or null.

But when you know that there is only one value (for your case it was my suggesstion 'cause you use and without aggregation) you can use it to modify the set (of dimension values) of such fields.

upd:

Oh, I've just realised.

I think when you do sum (only(a) - only(b)), you forget about sum() sets. You have to wrtite a set expression so that the totals can be calculated.

Re: set analysis for formula like sum(a-b)

Thu, 30 Aug 2012 09:36:29 GMT

Thanks.. after looking at the pivot.. it make sense now.

the only() works when i'm at the lowest value of my pivot, which means i have to expand all the rows.

once i collapse the roll, there's no data to it. that's why there's no value to the total line.

Is there a workaround? so that when it's collapsed, the overall total will work?

Thanks again.

Re: set analysis for formula like sum(a-b)

whiteline — Thu, 30 Aug 2012 09:57:34 GMT

Actually two workarounds:

1) use aggregation function if possible (like sum and so on)

2) use dimensionality() function to determine the level of expression being calculated.

If(dimensionality()=1, expression for subtotals, expression for other cells).

Re: set analysis for formula like sum(a-b)

Wed, 05 Sep 2012 13:10:37 GMT

Hi,

I still can't make my expression work.

I have made a similator in attached.

Bascially the percentage by row for zone and month and the selected product is good.

Just that the total percentage is not wanted.

I made a small table below, in the last column, if we can get the expression working, it will be great.

somebody ?

Thanks,

Re: set analysis for formula like sum(a-b)

whiteline — Wed, 05 Sep 2012 14:34:11 GMT

It calculates right:

(158.40 - 3.63 ) / 158.40 = 98%

You have to write true expression without 'Total Mode' Tricks.

The problem is that to achive 42.63 you need to apply fabs to every row while in your expressions

fabs(column(3) - Column(1))

it applies to the total values.

Use

=sum(aggr(fabs(sum(QUANTITY_FORECAST_F2) - sum(QUANTITY_REAL)), ZONE, PRODUCT, SUB ZONE))

it gives the sum of absolute differences for all rows and for each row.