topic Re: MTD in load statement in QlikView

MTD in load statement

Wed, 01 Jul 2015 10:51:48 GMT

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

Re: MTD in load statement

sunny_talwar — Wed, 01 Jul 2015 10:56:11 GMT

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?

Re: MTD in load statement

sunilkumarqv — Wed, 01 Jul 2015 11:21:19 GMT

Try this

Load *,

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

Load * from Datasoure

Re: MTD in load statement

Wed, 01 Jul 2015 11:48:19 GMT

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

];

Re: MTD in load statement

sunny_talwar — Wed, 01 Jul 2015 12:24:26 GMT

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

Re: MTD in load statement

Wed, 01 Jul 2015 12:53:06 GMT

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

];

Re: MTD in load statement

sunny_talwar — Wed, 01 Jul 2015 13:03:35 GMT

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

];

Re: MTD in load statement

Wed, 01 Jul 2015 13:16:23 GMT

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?

Re: MTD in load statement

sunny_talwar — Wed, 01 Jul 2015 13:20:24 GMT

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;

Re: MTD in load statement

Thu, 02 Jul 2015 08:32:15 GMT

thank you. Got it now.