Converting MQT to Monthly Data

[sushil353]

Hi All,

Need a help to understand the logic to get Monthly Numbers if i have data in MQT.

Month	MQT (rolling 3 month)	Desired Output (the output may vary based on the logic)
Jan2017	30	3
Feb2017	29	23
Mar2017	32	14
Apr2017	54	17
Mar2017	45	14
Apr2017	43	12 (x3)
May2017	36	10 (x2)
Jun2017	32	10 (x1)

in the above test data 32 for Jun2017 is sum of month Apr2017 to Jun2017. now i want to have those 3 numbers of Apr, May and Jun such that x1+x2+x3 = 32. similarly i need to have further 3 numbers of Mar, Apr and May such that the 3 number sum is 36 like x2+X3+X4 = 36.

How to find these monthly no ?

in above data file (x1), (x2),(x3)... are just for reference.

Regards,

sushil

[Anil_Babu_Samineni]

Looks impressive !!!

How you are going to split to each like Apr - 12, May-10, Jun-10 ?? And same for May2017 (14+12+10)..

Is there any logic behind to split of each month and which value it should be?

[sushil353]

Hi Anil,

10,10,12 are just numbers which i need to find.. these may be 8,12,12 as well..

That is the main issue that i dont have any reference of it..

[Anil_Babu_Samineni]

That means, We can provide Rand() values to each month for rolling 3 level and it should be sum with Current month?? Isn't it you mean ../

[sushil353]

Yes rt

[sushil353 · Accepted solution]

I have resolved the logic as:

T1:

LOAD * Inline

[

Date,Monthly,MQT

Mar-17,10,35

Apr-17,15,40

May-17,15,40

Jun-17,10,40

Jul-17,14,39

Aug-17,17,41

Sep-17,16,47

Oct-17,17,50

Nov-17,13,46

Dec-17,10,40

Jan-18,19,42

Feb-18,19,48

Mar-18,15,53

Apr-18,15,49

May-18,14,44

Jun-18,17,46

Jul-18,14,45

Aug-18,12,43

Sep-18,10,36

Oct-18,10,32

];

T2:

LOAD

if(RowNo()=1,MQT/3,if(RowNo()=2,MQT-(2*(Previous(MQT)/3)),MQT-RangeSum(peek(Monthly1,-1),peek(Monthly1,-2)))) as Monthly1 ,

Date as Date2,

MQT as MQT1,

RowNo() as rec

Resident T1;

Regards,

sushil

[Anonymous]

Hi Sushil,

This is kiran. Whats your contact number. Please send your contact details. My mailid is venkatkiran85@gmail.com