topic Change total calculation in QlikView

Change total calculation

Mon, 17 Oct 2011 07:57:12 GMT

Hello everyone,

I have a problem with the last row total of my table. I should first explain dimensions and calculatiıons of the table.

In dimentions there are three columns wihch are sectors, currencies and details.

And in claculation side I have two columns that shows the original figures of the first and last selected date.

The third column shows the difference of the first two columns.

In the fourth column evaluates the third column into USD equvalent. It simply multipliees the column three with the last days fx rate.

In other words the formulation I am using is In the last column you can see the rates I am using.

=column(3)* avg({$<APKO_RAPOR_TARIHI = {"$(=DATE(Max(APKO_RAPOR_TARIHI),'DD.MM.YYYY'))"} >} FXParite)

In the

In my case the problem is in the last row of the table (shwn in the red cicle).

In the last total row I need to sum the subtotals of the table. In other words in the fourth calculation column I need to show the total of 68810 instead of 388751.

Can anyone give me an idea?

Best Regards.

Change total calculation

Mon, 17 Oct 2011 08:24:14 GMT

Hi,

In the Cookbook from Rob Wundrlich (http://robwunderlich.com/downloads/) is a solution that might me helpful.

If you use if(rowno()=0,sum(x),sum(y)) or if(isnull(rowno())..... you can change the totals calculation.

Succes!

Change total calculation

Mon, 17 Oct 2011 11:14:00 GMT

Hi Halmar,

First of all thank you for your kind reply.

I have tried to chnage the calculation as IT was mentioned in the cookbook example. My final expression is below.

IF(Rowno()<>1 or Rowno()<>0,

column(3)* avg({$<APKO_RAPOR_TARIHI = {"$(=DATE(Max(APKO_RAPOR_TARIHI) 'DD.MM.YYYY'))"} >} FXParite) ,

sum(Rowno()=0))

As soon as I click "Apply" The file closes itself. Is there a reasion why this can happen? What critical point I am missing?

Best regards.

Re: Change total calculation

SunilChauhan — Mon, 17 Oct 2011 11:49:11 GMT

use

dimensionality() function

ex:

if(dimensionality()=0,100,1)

100 showing at the top of first row

use as per your need hope this may heelp

Change total calculation

Mon, 17 Oct 2011 12:27:45 GMT

Thank you Sunil.

However I change the formula , I am facing the same outcome.

The formula with dimensionality is below.

IF(dimensionality()>=2,

column(3)* avg({$<APKO_RAPOR_TARIHI = {"$(=DATE(Max(APKO_RAPOR_TARIHI),'DD.MM.YYYY'))"} >} FXParite)

,sum(dimensionality()=2))

I also tried to sum the false part of the If clause with sum(rowno()=0), but my file closed itself again.

Does anyone has other suggestions?

Change total calculation

SunilChauhan — Mon, 17 Oct 2011 12:31:26 GMT

in this expression

IF(dimensionality()>=2,

column(3)* avg({$<APKO_RAPOR_TARIHI = {"$(=DATE(Max(APKO_RAPOR_TARIHI),'DD.MM.YYYY'))"} >} FXParite)

,sum(dimensionality()=2))

you need to modify the sum(dimensionality()=2)

like

sum(if(dimensionality()=2,youramountfield))

Change total calculation

Mon, 17 Oct 2011 13:40:25 GMT

Dear Sunil,

After the modification of the formula, I am still facing the closing document problem.

The final version of the calculation is stated below.

IF(dimensionality()>1,

column(3)* avg({$<APKO_RAPOR_TARIHI = {"$(=DATE(Max(APKO_RAPOR_TARIHI),'DD.MM.YYYY'))"} >} FXParite),

sum(if(dimensionality()=2,column(4))))

What can be wrong with this expression?

Re: Change total calculation

psankepalli — Fri, 27 Oct 2017 03:26:47 GMT

Hi Hilmi,

I know I am replying for the old post, do you have any solution for the question you asked

Thanks