Proportionate Monthly Calculation

[#1]

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.

[swuehl · Accepted solution]

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')

[sunny_talwar]

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;

[#4]

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

It worked perfectly fine for me 😉