# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
New Contributor II

## Pivot Chart expression

Hi everyone,

I have a chart like

Currently my coll% is showing coll/Setup but what i want is that the coll amount should always be divided by the first setup only, i.e 40/100,20/100,10/100 giving me 40%,20%,10%. This should be respective of each and every month. Basically i want to take the setup amount where my setup month is same as my collection month.

Is there a way to this?

1 Solution

Accepted Solutions
MVP

## Re: Pivot Chart expression

I think this is the Coll% expression you are looking for

sum(CollectionAmount)/sum({<flag = {1}>} total <SetupMonth> SetupAmount)

Note the flag for SetupMonth = Nov is set for CollectionMonth = Nov (90). perhaps the flag should be Jan (110). The expression is interpreting the value correctly according to the flag.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
9 Replies
MVP

## Re: Pivot Chart expression

You may want to create a derived flag field in the load script to indicate the correct setup field. If you need more specific help, I suggest that you upload a small sample qvw with some representative sample data.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
New Contributor II

## Re: Pivot Chart expression

Hi,

Please find the attachments above. Let me know if you need anything else. Yes, i have created a flag but im not sure how to use it.

Thanks!

MVP

## Re: Pivot Chart expression

Try like:

sum(CollectionAmount)/Above(sum(SetupAmount), RowNo()-1,0)

Valued Contributor

## Re: Pivot Chart expression

Hi,

Try with:

Sum(CollectionAmount)/Max(TOTAL <SetupMonth> {<flag = 1>}SetupAmount)

Saludos

MVP

## Re: Pivot Chart expression

I think this is the Coll% expression you are looking for

sum(CollectionAmount)/sum({<flag = {1}>} total <SetupMonth> SetupAmount)

Note the flag for SetupMonth = Nov is set for CollectionMonth = Nov (90). perhaps the flag should be Jan (110). The expression is interpreting the value correctly according to the flag.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
New Contributor II

## Re: Pivot Chart expression

It wont work if i change my view

New Contributor II

## Re: Pivot Chart expression

Its working just fine. Dont worry about the Nov to Jan issue i will handle that. One more small help. I want all those setup amount with flag 1 to be shown beside the respective months as dimension so as to make it easy for the users to read the data. Can you please help me with that?

New Contributor II

## Re: Pivot Chart expression

Its just that i dont want to confuse the users with multiple setup amounts

MVP

## Re: Pivot Chart expression

Just use the denominator of the expression -

sum({<flag = {1}>} total <SetupMonth> SetupAmount)

I suggest that you do it as an expression rather than a dimension, so you can avoid the aggr(). Drag it into the desired position in the pivot.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein