Calculated dimension ?

[#1]

Hi

I Have one calculated scenario like

(Forecast - Ytd Actuals) /Remaining Months

attached is the example input source and Required out put also attached

For example

Okkaba Skin Jackets this product YTD Actual Value is (20+25+30)=75

Okkaba Skin Jackets this product Forecast Value is 40

Now according to fromula:  (40-75)/5= -7

now -7 should allocates to  remaning months (Aug,sep,oct, nov, dec)please see below data

here YTD months 7 and remaing months is 5

please help

Input file

Product id	Name	Month	Scenariao	Amount
10273	Halter Dress	1	Actual	33
10273	Halter Dress	3	Actual	30
10273	Halter Dress	4	Actual	15
10273	Halter Dress	5	Actual	35
10273	Halter Dress	7	Actual	20
10273	Halter Dress	6	Actual	18
10273	Halter Dress		Forecast	50
10452	Okkaba Skin Jackets	1	Actual	20
10452	Okkaba Skin Jackets	3	Actual	25
10452	Okkaba Skin Jackets	5	Actual	30
10452	Okkaba Skin Jackets		Forcast	40

Out put file

Product id	Name	Month	Scenariao	Amount
10273	Halter Dress	1	Actual	33
10273	Halter Dress	3	Actual	30
10273	Halter Dress	4	Actual	15
10273	Halter Dress	5	Actual	35
10273	Halter Dress	7	Actual	20
10273	Halter Dress	6	Actual	18
10273	Halter Dress	8	Forecast	-20.2
10273	Halter Dress	9	Forecast	-20.2
10273	Halter Dress	10	Forecast	-20.2
10273	Halter Dress	11	Forecast	-20.2
10273	Halter Dress	12	Forecast	-20.2
10452	Okkaba Skin Jackets	1	Actual	20
10452	Okkaba Skin Jackets	3	Actual	25
10452	Okkaba Skin Jackets	5	Actual	30
10273	Halter Dress	8	Forecast	-7
10273	Halter Dress	9	Forecast	-7
10273	Halter Dress	10	Forecast	-7
10273	Halter Dress	11	Forecast	-7
10273	Halter Dress	12	Forecast	-7

.

[effinty2112]

Hi Chaganti,

Your attachment doesn't match your description of the input file. Wrong attachment perhaps?

Kind regards

Andrew

[#3]

updated thanks for

[effinty2112]

Hi Chaganti,

I used this to load your data:

Data:

LOAD * INLINE [

    Product id, Name, Month, Scenario, Amount

    10273, Halter Dress, 1, Actual, 33

    10273, Halter Dress, 3, Actual, 30

    10273, Halter Dress, 4, Actual, 15

    10273, Halter Dress, 5, Actual, 35

    10273, Halter Dress, 7, Actual, 20

    10273, Halter Dress, 6, Actual, 18

    10273, Halter Dress, , Forecast, 50

    10452, Okkaba Skin Jackets, 1, Actual, 20

    10452, Okkaba Skin Jackets, 3, Actual, 25

    10452, Okkaba Skin Jackets, 5, Actual, 30

    10452, Okkaba Skin Jackets, , Forecast, 40

];

And got this straight table:

Product id	Name	Month	Scenario	Amount
10273	Halter Dress	1	Actual	33
10273	Halter Dress	2	Actual	0
10273	Halter Dress	3	Actual	30
10273	Halter Dress	4	Actual	15
10273	Halter Dress	5	Actual	35
10273	Halter Dress	6	Actual	18
10273	Halter Dress	7	Actual	20
10273	Halter Dress	8	Forecast	-20.2
10273	Halter Dress	9	Forecast	-20.2
10273	Halter Dress	10	Forecast	-20.2
10273	Halter Dress	11	Forecast	-20.2
10273	Halter Dress	12	Forecast	-20.2
10452	Okkaba Skin Jackets	1	Actual	20
10452	Okkaba Skin Jackets	2	Actual	0
10452	Okkaba Skin Jackets	3	Actual	25
10452	Okkaba Skin Jackets	4	Actual	0
10452	Okkaba Skin Jackets	5	Actual	30
10452	Okkaba Skin Jackets	6	Forecast	-5
10452	Okkaba Skin Jackets	7	Forecast	-5
10452	Okkaba Skin Jackets	8	Forecast	-5
10452	Okkaba Skin Jackets	9	Forecast	-5
10452	Okkaba Skin Jackets	10	Forecast	-5
10452	Okkaba Skin Jackets	11	Forecast	-5
10452	Okkaba Skin Jackets	12	Forecast	-5

For the Okkaba jackets the total Forecast - total Sales was 40 - 75 = -35 then / 7 gives -5.

The dimensions are

Product id,

Name,

=ValueLoop(1,12), labelled as Month

The expressions are

Scenario:

if(ValueLoop(1,12)<=Aggr(NODISTINCT Max(Month),[Product id]),'Actual','Forecast')

Amount:

if(ValueLoop(1,12)<=Aggr(NODISTINCT Max(Month),[Product id]),sum(if(Month = ValueLoop(1,12),

Aggr(only({$<Scenario = {'Actual'}>}Amount),[Product id],Month)))

,(sum({$<Scenario = {'Forecast'}>}Amount)-sum({$<Scenario = {'Actual'}>}Amount)) / (12-Max({$<Scenario = {'Actual'}>}Month)))

Cheers

Andrew

[#5]

thanks for giving i used same logic but its not working

can you please provide your qvw.

thanks

[effinty2112]

Hi Chaganti,

qvw attached.

Cheers

Andrew

[#7]

hi

its looks good but That forecast value it should allocated from  after YTD Month onwords

but in your example it allocating previous months also

[effinty2112 · Accepted solution]

The qvw allocates it from the first month after the last sale.

Try these expressions instead then

Scenario:

if(ValueLoop(1,12)<Month(Today()),'Actual','Forecast')

Amount:

if(ValueLoop(1,12)<Month(Today()),sum(if(Month = ValueLoop(1,12),

Aggr(only({$<Scenario = {'Actual'}>}Amount),[Product id],Month)))

,(sum({$<Scenario = {'Forecast'}>}Amount)-sum({$<Scenario = {'Actual'}>}Amount)) / (12+1-Month(Today())))

regards

Andrew