MTD in load statement

[#1]

Hello community,

I have year, month and a year-month field like so: 2015-01... 2015-12. I would like to calculate the average for each month for two fields in the load statement.

Example for one field:

AVG of 01 = valueof(01)/1

AVG of 02 = valueof(01 + 02) / 2

...

AVG of 05 = valueof(01 + 02 + 03 +  04 + 05) / 5

I believe I need this in the load statement because I don't know how it would work with set analysis while for example only month 05 is selected. Because with that one month selection it should display the calculated value.

Hope you can help

Thank you,

Thorsten

[sunny_talwar]

It can work in script as well as can be done in the front end also. Would you be able to share some sample data or a sample qvw file?

[sunilkumarqv]

Try this

Load *,

RangeSum(Fieldvalue,Peek('Avgvalue'))                         As Avgvalue;

Load * from Datasoure

[#4]

Here's a sample:

LOAD * INLINE [

    YearMonth, Year, Month, myInputValue, myOutputValue

    2015-01, 2015, 01, "0,3", "0,3"

    2015-02, 2015, 02, "0,42", "0,36"

    2015-03, 2015, 03, "0,16", "0,293"

    2015-04, 2015, 04, "0,24", "0,28"

    2015-05, 2015, 05, "0,35", "0,294"

    2015-06, 2015, 06, "0,88", "0,391"

    2015-07, 2015, 07

    2015-08, 2015, 08

    2015-09, 2015, 09

    2015-10, 2015, 10

    2015-11, 2015, 11

    2015-12, 2015, 12

];

[sunny_talwar]

Attaching the front end and backend solution. You may need some changes when you bring in more years

[#6]

thank you. I understand my problem now 🙂
I do have other columns like plant, ID and so on.

I added the plant column here and it won't output the correct values:

What options do I have in this scenario?

Table:

LOAD *,

     MySum/MyCount as MyNewOutput;

LOAD *,

     RangeSum(myInputValue, Alt(Peek('MySum'), 0)) as MySum,

     RangeSum(1, Alt(Peek('MyCount'), 0)) as MyCount;

LOAD * INLINE [

    YearMonth, Year, Month, myInputValue, myOutputValue, Plant

    2015-01, 2015, 01, 0.3, 0.3, A

    2015-02, 2015, 02, 0.42, 0.36, A

    2015-03, 2015, 03, 0.16, 0.293, A

    2015-04, 2015, 04, 0.24, 0.28, A

    2015-05, 2015, 05, 0.35, 0.294, A

    2015-06, 2015, 06, 0.88, 0.391, A

    2015-07, 2015, 07, , , A

    2015-08, 2015, 08, , , A

    2015-09, 2015, 09, , , A

    2015-10, 2015, 10, , , A

    2015-11, 2015, 11, , , A

    2015-12, 2015, 12, , , A

    2015-01, 2015, 1, 0.3, 0.3, B

    2015-02, 2015, 2, 0.42, 0.36, B

    2015-03, 2015, 3, 0.16, 0.293, B

    2015-04, 2015, 4, 0.24, 0.28, B

    2015-05, 2015, 5, 0.35, 0.294, B

    2015-06, 2015, 6, 0.88, 0.391, B

    2015-07, 2015, 7, , , B

    2015-08, 2015, 8, , , B

    2015-09, 2015, 9, , , B

    2015-10, 2015, 10, , , B

    2015-11, 2015, 11, , , B

    2015-12, 2015, 12, , , B

];

[sunny_talwar · Accepted solution]

Slight change to the script:

Table:

LOAD *,

    MySum/MyCount as MyNewOutput;

LOAD *,

    If(Plant = Peek('Plant'), RangeSum(myInputValue, Alt(Peek('MySum'), 0)), myInputValue) as MySum,

    If(Plant = Peek('Plant'), RangeSum(1, Alt(Peek('MyCount'), 0)), 1) as MyCount;

LOAD * INLINE [

    YearMonth, Year, Month, myInputValue, myOutputValue, Plant

    2015-01, 2015, 01, 0.3, 0.3, A

    2015-02, 2015, 02, 0.42, 0.36, A

    2015-03, 2015, 03, 0.16, 0.293, A

    2015-04, 2015, 04, 0.24, 0.28, A

    2015-05, 2015, 05, 0.35, 0.294, A

    2015-06, 2015, 06, 0.88, 0.391, A

    2015-07, 2015, 07, , , A

    2015-08, 2015, 08, , , A

    2015-09, 2015, 09, , , A

    2015-10, 2015, 10, , , A

    2015-11, 2015, 11, , , A

    2015-12, 2015, 12, , , A

    2015-01, 2015, 1, 0.3, 0.3, B

    2015-02, 2015, 2, 0.42, 0.36, B

    2015-03, 2015, 3, 0.16, 0.293, B

    2015-04, 2015, 4, 0.24, 0.28, B

    2015-05, 2015, 5, 0.35, 0.294, B

    2015-06, 2015, 6, 0.88, 0.391, B

    2015-07, 2015, 7, , , B

    2015-08, 2015, 8, , , B

    2015-09, 2015, 9, , , B

    2015-10, 2015, 10, , , B

    2015-11, 2015, 11, , , B

    2015-12, 2015, 12, , , B

];

[#8]

So do I understand correctly, that when I have 7 columns that are not "equal", I have to put them in above statements?

If(Plant = Peek('Plant') AND ID = Peek('ID'), RangeSum(myInputValue, Alt(Peek('MySum'), 0)), myInputValue) as MySum

and so on?

[sunny_talwar]

This is just saying that check the previous row, if the plant name is same then do the RangeSum, else start again. One thing I missed is the Order By Statement, which is very important. In the sample you gave, the data was already sorted in the correct way, but when you are pulling in from a database, you need to add Order By statement to make sure all the rangesum stuff is together. In the above scenario I would have added this statement

Order by Plant, YearMonth;

[#10]

thank you. Got it now.