Replicating Excel formula in QV.

[#1]

Hi All,

we are having a requirement given below (also attached the excel). Looks pretty straight forward in excel but difficult to replicate in QlikView.

The values in the column "CarryForward" needs to be taken from previous month of "Final" for the corresponding Team and this value needs to be used in the Final again. (Final = CarryForward + Amount).

We are unable to bring the previous value in the Final to the CarryForward.

Please let us know if you need any more details.

Month	Team	CarryForward	Amount	Final
1	Team1	0	100	100
1	Team2	0	260	260
2	Team1	100	500	600
2	Team2	260	240	500
3	Team1	600	580	1180
3	Team2	500	566	1066

Thanks,
Sijo Joseph

[datanibbler]

Hi,

in QlikView there is the PEEK() function - or PREVIOUS().

Both of these functions let you populate one record with a value from the previously read record (the one above if you have it sorted by month).

HTH

Best regards,

DataNibbler

[MK_QSL]

Provide proper sample data... we would work for your expected result...

[kuba_michalik]

Is that supposed to be done in script, or in the frontend (a straigtht chart for example)?

[#5]

Check out the QVW attached.

Load Script:

Raw:

LOAD * INLINE [

    Month, Team, Amount

    1, Team1, 100

    1, Team2, 260

    2, Team1, 500

    2, Team2, 240

    3, Team1, 580

    3, Team2, 566

];

Final:

Load

  Month,

  Team,

  Amount,

  If(Team = Previous(Team) and Month = Previous(Month)+1, Previous(Amount), 0) as CarryForward,

  If(Team = Previous(Team) and Month = Previous(Month)+1, Previous(Amount), 0) + Amount as Final

Resident Raw

Order by Team asc,Month asc;

Drop table Raw;

[#6]

Hi All,

We need this to be done in the front end, ie a pivot table or straight table.

Thanks,

Sijo

[#7]

Hi All,

We need this to be done in the front end, ie a pivot table or straight table.

Thanks,

Sijo