topic Re: Replicating Excel formula in QV. in QlikView

Replicating Excel formula in QV.

Tue, 04 Nov 2014 15:25:12 GMT

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

Re: Replicating Excel formula in QV.

datanibbler — Tue, 04 Nov 2014 15:29:09 GMT

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

Re: Replicating Excel formula in QV.

MK_QSL — Tue, 04 Nov 2014 15:35:04 GMT

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

Re: Replicating Excel formula in QV.

kuba_michalik — Tue, 04 Nov 2014 15:39:46 GMT

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

Re: Replicating Excel formula in QV.

Tue, 04 Nov 2014 15:47:56 GMT

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;

Re: Replicating Excel formula in QV.

Tue, 04 Nov 2014 16:07:43 GMT

Hi All,

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

Thanks,

Sijo

Re: Replicating Excel formula in QV.

Tue, 04 Nov 2014 16:13:55 GMT

Hi All,

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

Thanks,

Sijo