topic Re: Converting MQT to Monthly Data in QlikView

Converting MQT to Monthly Data

sushil353 — Tue, 19 Dec 2017 06:27:50 GMT

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

Re: Converting MQT to Monthly Data

Anil_Babu_Samineni — Tue, 19 Dec 2017 06:40:05 GMT

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?

Re: Converting MQT to Monthly Data

sushil353 — Tue, 19 Dec 2017 06:49:47 GMT

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

Re: Converting MQT to Monthly Data

Anil_Babu_Samineni — Tue, 19 Dec 2017 06:51:43 GMT

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 ../

Re: Converting MQT to Monthly Data

sushil353 — Tue, 19 Dec 2017 07:05:24 GMT

Yes rt

Re: Converting MQT to Monthly Data

sushil353 — Tue, 19 Dec 2017 08:42:50 GMT

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

Re: Converting MQT to Monthly Data

Anonymous — Fri, 05 Jan 2018 10:29:38 GMT

Hi Sushil,

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