topic Re: Proportionate Monthly Calculation in QlikView

Proportionate Monthly Calculation

Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

How can I calculate proportionate value in Qlikview table

For Eg : I have a data say :

Business	Yearly Target
Business A	5000
BusinessB	7000
Business C	9000
Business D	2000

So I want to put an argument where it will calculate the data proportionately for each month, like for Jan target for Business A would be 5000/12, for Feb (5000/12)*2 and so on

What Dynamic formula should I write so that it get updated when I choose the month in drop down.

Re: Proportionate Monthly Calculation

swuehl — Fri, 17 Jun 2016 15:17:04 GMT

Maybe

=Sum([Yearly Target])/12 * Max(Month)

assuming you are selecting a month value in your list box that shows a numeric representation (like you have used Month(DATEFIELD) to create the Month field).

If you are just using a variable input box drop down select, maybe like

=Sum([Yearly Target])/12 * Match(vMonth, 'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

Re: Proportionate Monthly Calculation

sunny_talwar — Fri, 17 Jun 2016 16:29:50 GMT

May be something along these lines?

Table:

LOAD Business,

[Yearly Target],

[Yearly Target]/12 as [Monthly Target];

LOAD * INLINE [

Business, Yearly Target

Business A, 5000

BusinessB, 7000

Business C, 9000

Business D, 2000

];

Join(Table)

LOAD Month(Date#(Month, 'MMM')) as Month

INLINE [

Month

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

];

FinalTable:

LOAD Business,

Month,

[Monthly Target],

Month * [Monthly Target] as [Cumulative Monthly Target]

Resident Table;

Re: Proportionate Monthly Calculation

Mon, 20 Jun 2016 09:36:42 GMT

Thanks Sunny & Swuehl. You guys are genious !!!!

It worked perfectly fine for me 😉