Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?
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..
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 ../
Yes rt
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
Hi Sushil,
This is kiran. Whats your contact number. Please send your contact details. My mailid is venkatkiran85@gmail.com