topic Re: How to do Roll up Totals? in QlikView

How to do Roll up Totals?

Wed, 26 Feb 2014 07:38:58 GMT

HI,

i am doing a POC for a General Ledger application. I need to do roll up totals at Control Account levels. the totals that appear are reconciled. the Chart of Account is 5 levels. i want to be able to calculate totals at above four levels reaching up to first level (Type of Account: Assets, Expense etc.)

so in my case i want total at:

- Assets

- Fixed Assets

- Tangible Operating Fixed Assets

- Land

i just cant figure out how to do it.

the object type is list box and i have configured it to appear as tree by specifying '-'

i use following expression in calculating the amount: =$(vBal) and value of vBal is following expression (line copy/pasted):

LET vBal = 'if (sum(VD_DR_AMT) > sum(VD_CR_AMT),(sum(VD_DR_AMT) - sum(VD_CR_AMT)),if (sum(VD_CR_AMT) > sum(VD_DR_AMT),(sum(VD_CR_AMT) - sum(VD_DR_AMT)),0))';

Appreciated your reply in advance.

Thanks,

Sibtain

Re: How to do Roll up Totals?

Wed, 26 Feb 2014 07:55:34 GMT

Hi Sibtain

Why do you want to do it in List Box? I think Straight Table/Pivot Table would be better

Lukasz

Re: How to do Roll up Totals?

jonathandienst — Wed, 26 Feb 2014 07:57:41 GMT

From your image it looks like you are trying to report in a list box. You will not be able to show totals etc. Rather use a pivot table - this is the correct object for your requirements. Use the top 4 levels as dimensions and set partial totals where required. Actually I would use all 5 levels and make sure that I have a partial total at the required levels. Now the users can selectively expand to drill down into the lower level details.

Your expression does not make a lot of sense and I don't think it will total correctly. It is just returning the absolute values, and is arithmetically identical to Fabs(sum(VD_DR_AMT) - sum(VD_CR_AMT)). If you want to gross up positive and negative values, create two expressions:

RangeMax(sum(VD_DR_AMT) - sum(VD_CR_AMT), 0) // for positive values

RangeMin(sum(VD_DR_AMT) - sum(VD_CR_AMT), 0) // for negative values or

RangeMax(sum(VD_CR_AMT) - sum(VD_DR_AMT), 0) // displaying them as positive

HTH

Jonathan

Re: How to do Roll up Totals?

Wed, 26 Feb 2014 08:56:42 GMT

Hi Jonathan,

Thanks for your reply.

My totals are correct, as i am trying to calculate the ledger balance and have tallied these figures also.

However, your mention of Range series of function has potentially solved another problem not yet mentioned.

I am definitely going to try it and the above functions as well.

On the other hand, can you tell me any function or way out to ignore NULL values in set expression and count on values which actually have values (excluding null value set from actual set)

Thanks,

Sibtain

Re: How to do Roll up Totals?

Wed, 26 Feb 2014 08:59:55 GMT

Hi Lukasz,

Thanks for pointing out to Pivot Table, although i had tried it but not with "each level of chart of account as a dimension" have been trying as a whole. so far it worked.

BUT what is really wanted to do was generating these dimensions dynamically as Chart of Account would change from company to company. It would kind of a Hardcoded design, although it works.

Any thoughts on this?

Thanks,

Sibtain

Re: How to do Roll up Totals?

jonathandienst — Wed, 26 Feb 2014 13:36:13 GMT

Sibtain

Null values are generally ignored in aggregation functions, so Count() will not count null values, Sum() will treat null values as zero, Avg() etc also do not consider null values. So you can usually rely on the default handling of QV to take care of this.

The difficult part is when DO need to include the nulls. But set expressions (which is just a filtering technique) cannot select a null (just like you cannot yourself select a null in a list box or table). If, for some reason you do need to explicitly exclude nulls, then you can do this:

Sum({<Account={'*'}>} Balance)

Will select all possible values and exclude nulls from the sum.

Jonathan