topic Re: Calculation in QlikView

Calculation

upaliwije — Tue, 12 Jun 2018 16:18:29 GMT

Hi Friends

I have apivot table in my QV document which is attached here to. In this data I have 4 regions with 5 years Premium data relating different locations. at Regular Intervals Locations are reshuffled among the regions. I have calculated the growth as shown in the Pivot table. But I want to calculate the growth in a different way. That is I want to calculate the growth for a particular year say 2015 based on the premium of same loaction of year 2014. In other words realocation of locations to region sholud be disregarded.

Eg:

In the above example a new column should be created to show premium for year 2013 disregarding the region of the loaction. In other words premium should be shown for the location where there is zero values in 2013. Then growth should be calculated based on the premium of new coloumn.

Pls help me to do this

Re: Calculation

sunny_talwar — Tue, 12 Jun 2018 16:24:07 GMT

In the above example a new column should be created to show premium for year 2013 disregarding the region of the loaction. In other words premium should be shown for the location where there is zero values in 2013. Then growth should be calculated based on the premium of new coloumn.

Pls help me to do this

Premium for 2013? What values are you expecting to see for 2013 premium

Re: Calculation

upaliwije — Tue, 12 Jun 2018 16:27:04 GMT

Dear Sunny,

The values for the zero locations are shown against a different regions. I want to pick such vales and show in new column

Re: Calculation

sunny_talwar — Tue, 12 Jun 2018 16:28:57 GMT

Zero locations are shown against a different regions? I have no idea what that means

Re: Calculation

upaliwije — Tue, 12 Jun 2018 16:32:51 GMT

Sorry

New column should be for 2014 not for 2013

Re: Calculation

sunny_talwar — Tue, 12 Jun 2018 16:33:33 GMT

With what values?

Re: Calculation

upaliwije — Tue, 12 Jun 2018 16:40:32 GMT

For Example MK00 location (BCOD) is attached to REGION 2 IN 2014 where as in 2015 it is under Region one

Re: Calculation

upaliwije — Tue, 12 Jun 2018 16:42:54 GMT

Sunny

in some locations values are not there due to the fact that I am showing sample data. But I think loaction MK03 is a classic example.

Re: Calculation

sunny_talwar — Tue, 12 Jun 2018 16:49:06 GMT

So, this is needed only for 2014 or all the years?

Re: Calculation

upaliwije — Tue, 12 Jun 2018 16:49:28 GMT

new Column and growth should be shown like below

Re: Calculation

upaliwije — Tue, 12 Jun 2018 16:49:59 GMT

For all Years

Re: Calculation

sunny_talwar — Tue, 12 Jun 2018 16:50:20 GMT

May be this

Sum(TOTAL <BCOD, YEAR> Premium)

Re: Calculation

upaliwije — Tue, 12 Jun 2018 17:06:41 GMT

Thanks Sunny

Then all Regions show the same values.

Re: Calculation

sunny_talwar — Tue, 12 Jun 2018 17:22:52 GMT

Try this

Sum(Aggr(Sum(TOTAL <BCOD, YEAR> Premium), RM, BCOD, YEAR))

Re: Calculation

upaliwije — Wed, 13 Jun 2018 04:09:18 GMT

Thanks Sunny for your effort and help. Still I do not get the desired results.

Thanks again

Re: Calculation

sunny_talwar — Wed, 13 Jun 2018 08:59:33 GMT

I am sure you are not, but I don't even understand what is desired

Re: Calculation

upaliwije — Thu, 14 Jun 2018 03:50:29 GMT

Thanks Sunny

For a single location I have given below the system output and Desired output. It is much thankful if you can guide me to do it thta way.