topic Re: Calculation problem in QlikView

Calculation problem

Tue, 21 Jul 2015 11:29:21 GMT

Hi all,

So I am experiencing an issue where I have made several calculations that are consistently correct, also after filtering, but when I filter, there are other calculations that go wrong. To illustrate what I mean, I will show two images (a "before and after") of the problem at hand.

As you can see above the percentages add and I can attest to the fact that the numbers are true. However, if I select one or more business areas, what I get is a correct percentage for those exact ones I've selected, but all the others go haywire. For example:

Here I have selected only 1 business area (Jackets) and as such I get the value 100,0%, which then of course is correct. But my other values are not 0% as they then obviously should be. Instead it all adds up to a total of about 494,6%. My formula used to get the number is as follows:

=NUM( (SUM({<BLART={'K1','K2','K3','K4','KR'}, [BA Name]={'Jackets'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM({<BLART={'KA'}, [BA Name]={'Jackets'}>}#ActualNetSpend_NOK)) / (SUM(TOTAL{<BLART={'K1','K2','K3','K4','KR'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM(TOTAL{<BLART={'KA'}>}#ActualNetSpend_NOK)),'#,#%')

Can anyone see an explanation for this? Thanks!

Re: Calculation problem

Anonymous — Tue, 21 Jul 2015 11:45:41 GMT

Hi Espen,

How inportant is this filter: "[BA Name]={'Jackets'}". It seems like you are making the calculation in relation to Jackets and not everything. Try removing the filter [BA Name]={'Jackets'} and see if you get the results you are expecting

Re: Calculation problem

teiswamsler — Tue, 21 Jul 2015 11:51:55 GMT

hi Espen

try this, after TOTAL put in < [BA Name] > - cant read the correct dimension name

NUM( (SUM({<BLART={'K1','K2','K3','K4','KR'}, [BA Name]={'Jackets'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM({<BLART={'KA'}, [BA Name]={'Jackets'}>}#ActualNetSpend_NOK)) / (SUM(TOTAL < [BA Name] > {<BLART={'K1','K2','K3','K4','KR'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM(TOTAL < [BA Name] >{<BLART={'KA'}>}#ActualNetSpend_NOK)),'#,#%')

/Teis

Re: Calculation problem

marcus_sommer — Tue, 21 Jul 2015 11:53:55 GMT

I notice that you have no brakets between your normal sum and the total sum. Therefore the second expression will be divide through the third one and the others will be added.

Further you have hard-coded values 'jackets' within the set analysis which might not always what you want - you could replace it with a query on the selections with getfieldselections() or p().

- Marcus

Re: Calculation problem

Tue, 21 Jul 2015 12:13:31 GMT

Hi Marcus,

I will have a look at the parenthesis. However, as for the Jackets comment. I should have mentioned that in the leftmost Percentage (see image above) I have hard-coded Jackets, in the next one I have hard-coded Topsides, and so on. So in each image I have hard-coded whatever Business Area that image is showing. That isn't wrong, is it?

Re: Calculation problem

rubenmarin — Tue, 21 Jul 2015 12:23:29 GMT

Hi Espen, maybe this expression:

=NUM( (SUM({<BLART={'K1','K2','K3','K4','KR'}, [BA Name]*={'Jackets'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM({<BLART={'KA'}, [BA Name]*={'Jackets'}>}#ActualNetSpend_NOK)) / ((SUM(TOTAL{<BLART={'K1','K2','K3','K4','KR'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM(TOTAL{<BLART={'KA'}>}#ActualNetSpend_NOK)),'#,#%'))

Note that in the first part of division I cahnged "[BA Name]={'Jackets'}" to "[BA Name]*={'Jackets'}" (using *=)

Your expressión takes the sales of each BA and divides by the selection, so for TOPSIDES you take his NetSpend and the result is divided for the NetSpend of the selection, wich in the example is 'Jackets'.

Or, if you don't want [BA Name] selections affect your percentages use:

=NUM( (SUM({<BLART={'K1','K2','K3','K4','KR'}, [BA Name]={'Jackets'}, BUZEI={'001'}>}#ActualNetSpend_NOK) + SUM({<BLART={'KA'}, [BA Name]={'Jackets'}>}#ActualNetSpend_NOK)) / ((SUM(TOTAL{<BLART={'K1','K2','K3','K4','KR'}, BUZEI={'001'}, [BA Name]>}#ActualNetSpend_NOK) + SUM(TOTAL{<BLART={'KA'}, [BA Name]>}#ActualNetSpend_NOK)),'#,#%'))

Re: Calculation problem

sgrice — Tue, 21 Jul 2015 12:30:19 GMT

If you do not want them to change when selection are made simple put 1 in the set

{1<

or if you just want it to ignore a selected field add to set

theFieldToIgnore=,

where theFieldToIgnore is the field you want to be ignored