Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculated dimension ?

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 idNameMonthScenariaoAmount
10273Halter Dress1Actual33
10273Halter Dress3Actual30
10273Halter Dress4Actual15
10273Halter Dress5Actual35
10273Halter Dress7Actual20
10273Halter Dress6Actual18
10273Halter DressForecast50
10452Okkaba Skin Jackets1Actual20
10452Okkaba Skin Jackets3Actual25
10452Okkaba Skin Jackets5Actual30
10452Okkaba Skin JacketsForcast40

Out put file

Product idNameMonthScenariaoAmount
10273Halter Dress1Actual33
10273Halter Dress3Actual30
10273Halter Dress4Actual15
10273Halter Dress5Actual35
10273Halter Dress7Actual20
10273Halter Dress6Actual18
10273Halter Dress8Forecast-20.2
10273Halter Dress9Forecast-20.2
10273Halter Dress10Forecast-20.2
10273Halter Dress11Forecast-20.2
10273Halter Dress12Forecast-20.2
10452Okkaba Skin Jackets1Actual20
10452Okkaba Skin Jackets3Actual25
10452Okkaba Skin Jackets5Actual30
10273Halter Dress8Forecast-7
10273Halter Dress9Forecast-7
10273Halter Dress10Forecast-7
10273Halter Dress11Forecast-7
10273Halter Dress12Forecast-7

.

1 Solution

Accepted Solutions
effinty2112
Master
Master

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

View solution in original post

7 Replies
effinty2112
Master
Master

Hi Chaganti,

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

Kind regards

Andrew

Not applicable
Author

updated thanks for

effinty2112
Master
Master

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
10273Halter Dress1Actual33
10273Halter Dress2Actual0
10273Halter Dress3Actual30
10273Halter Dress4Actual15
10273Halter Dress5Actual35
10273Halter Dress6Actual18
10273Halter Dress7Actual20
10273Halter Dress8Forecast-20.2
10273Halter Dress9Forecast-20.2
10273Halter Dress10Forecast-20.2
10273Halter Dress11Forecast-20.2
10273Halter Dress12Forecast-20.2
10452Okkaba Skin Jackets1Actual20
10452Okkaba Skin Jackets2Actual0
10452Okkaba Skin Jackets3Actual25
10452Okkaba Skin Jackets4Actual0
10452Okkaba Skin Jackets5Actual30
10452Okkaba Skin Jackets6Forecast-5
10452Okkaba Skin Jackets7Forecast-5
10452Okkaba Skin Jackets8Forecast-5
10452Okkaba Skin Jackets9Forecast-5
10452Okkaba Skin Jackets10Forecast-5
10452Okkaba Skin Jackets11Forecast-5
10452Okkaba Skin Jackets12Forecast-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

Not applicable
Author

thanks for giving i used same logic but its not working

can you please provide your qvw.

thanks

effinty2112
Master
Master

Hi Chaganti,

qvw attached.

Cheers

Andrew

Not applicable
Author

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
Master
Master

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