Need to Create the Red marked column in QV.

[Anonymous]

Hi All,

I need to calculate the Migrate column(shown in Red) in QlikView.  i Have the Quarter and Migrate# in my application as the same format you see below.

Can someone help to create the 3rd column(Migrate) in QV. The Total of Migrate#(134) is the start line and you need subtract the Migrate# number for the first time with the 1st quarter(134-4 =130) . The 130 here would be static till you find the new number in Migrate# and once you find subtract from it.  Hope i could try to explain properly. Please help me with the solution in QV.

Quarter	Migrate#	Migrate	Explanation
			134
Q1-00	4	130	134-4
Q1-15		130
Q2-15		130
Q3-15		130
Q4-15		130
Q1-16	1	129	130-1
Q2-16		129
Q3-16		129
Q4-16	14	115	129-14
Q1-17	12	103	115-12
Q2-17	20	83	103-20
Q3-17	1	82	83-1
Q4-17	3	79	82-3
Q1-18	1	78	79-1
Q2-18	2	76	78-2
Q3-18	24	52	76-24
Q4-18	37	15	52-37
Q1-19	15	0	15-15
	134

[MarcoWedel]

Hi,

maybe this could be a solution:

tabTemp:
LOAD Dual(F1,AddMonths(Date#(Right(F1,2),'YY'),(Mid(F1,2,1)-1)*3)) as Quarter,
    Migrate#
FROM [https://community.qlik.com/servlet/JiveServlet/download/1176614-257384/Migrate.xlsx] (ooxml, embedded labels, table is Sheet1, filters(Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 20))));

Join

LOAD Sum(Migrate#) as TotalMigrate#
Resident tabTemp;

tabResult:
LOAD Quarter,
    Migrate#,
    RangeSum(Alt(Peek(Migrate),TotalMigrate#),-Migrate#) as Migrate
Resident tabTemp
Order By Quarter;

DROP Table tabTemp;

hope this helps

regards

Marco

[Anonymous]

Thanks Marco for the reply.

Can you please explain me how the below calculation works.

  RangeSum(Alt(Peek(Migrate),TotalMigrate#),-Migrate#) as Migrate 

Thanks

Ankit

[MarcoWedel · Accepted solution]

Hi,

this expression returns the sum of -Migrate# and either the last loaded "Migrate" value or, if not existing, i.e. in the first row, the TotalMigrate# number.

I used RangeSum because it returns results even if one parameter has no numeric value; it subtracts 0 in case of a non existing Migrate# value.

see also:

RangeSum

Alt

Peek

Please close your thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

[Anonymous]

Thanks Marco.. It was really helpful.

[MarcoWedel]

you're welcome

regards

Marco