topic Re: Distribute the value to the allocated months in QlikView

Distribute the value to the allocated months

Anonymous — Fri, 01 Jun 2018 11:59:28 GMT

Hi friends,

I have to generate

Just need your help,

I need to split the value in that excel sheet.

As per data in the attached sheet:

If Column D is blank
Divide the value in Column B /12
Place that value in each month from the Sale Start Date month
E.g Sale ID 1, value '178,283' /12 = '14,857' which is placed in each month from the start of the Sale Start date, in this case, was Dec-16
Sale ID 4: Value 57,757/12 which is equal to '4,813' I placed in each month from sale start month which is Apr-18 till Aug-20

Regards

Tony

LOAD
[Annual Sales],
[Sale Start Date],
[Sale End Date],
[Profit End Date],

//If [Sale End Date] is blank consider the date to be Apr-2021
Month([Sale Start Date]) as [Sale Start Month],
Year([Sale Start Date]) as [Sale Start Year],
Month([Sale End Date]) as [Sale End Month],
Year([Sale End Date]) as [Sale End Year],
Month([Profit End Date]) as [Profit End Month],
Year([Profit End Date]) as [Profit End Year],

Re: Distribute the value to the allocated months

sunny_talwar — Fri, 01 Jun 2018 18:56:30 GMT

Is this something you are looking to do while loading the data or is this on the front end chart?

Re: Distribute the value to the allocated months

Anonymous — Fri, 01 Jun 2018 19:35:47 GMT

Hi Sunny,

Yes. the data is in excel, I have attached.

In the excel only the 4 inputs fields will get loaded.

Annual Sales

Sale Start Date

Sale End Date

Profit End Date

Given the data set, I want those calculated fields to do a loop or assign a number based on the months they belong.

I hope I was able to explain. 100Yrs, Tom P just mentioned your name.

Regards

Tony