topic Calculated dimension ? in QlikView

Calculated dimension ?

Thu, 24 Aug 2017 11:15:27 GMT

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

Re: Calculated dimension ?

effinty2112 — Thu, 24 Aug 2017 12:46:38 GMT

Hi Chaganti,

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

Kind regards

Andrew

Re: Calculated dimension ?

Thu, 24 Aug 2017 12:51:14 GMT

updated thanks for

Re: Calculated dimension ?

effinty2112 — Thu, 24 Aug 2017 14:26:42 GMT

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

Re: Calculated dimension ?

Thu, 24 Aug 2017 15:09:00 GMT

thanks for giving i used same logic but its not working

can you please provide your qvw.

thanks

Re: Calculated dimension ?

effinty2112 — Thu, 24 Aug 2017 15:18:03 GMT

Hi Chaganti,

qvw attached.

Cheers

Andrew

Re: Calculated dimension ?

Thu, 24 Aug 2017 15:40:23 GMT

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

but in your example it allocating previous months also

Re: Calculated dimension ?

effinty2112 — Thu, 24 Aug 2017 16:21:40 GMT

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