# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Contributor

## Logic implementation

Hello All,

I need your assistance for below requirement, I have a table like this-

 ILITM ILMCU Transaction_Date #Transaction_Qty #ONHAND_QTY 72331 11091 15-09-2015 6930 9450 72331 11091 07-08-2015 8820 9450 72331 11091 23-05-2015 8800 9450 72302 11091 12-09-2015 16000 1024500 72302 11091 12-09-2015 16500 1024500 72302 11091 12-09-2015 24000 1024500 72302 11091 12-09-2015 26000 1024500

Client requirement is as below in Expected column,

 ILITM ILMCU Transaction_Date #Transaction_Qty #ONHAND_QTY Expected 72331 11091 15-09-2015 6930 9450 6930 72331 11091 07-08-2015 8820 9450 2520 72331 11091 23-05-2015 8800 9450 -6300 72302 11091 12-09-2015 16000 1024500 16000 72302 11091 12-09-2015 16500 1024500 1008500 72302 11091 12-09-2015 24000 1024500 992000 72302 11091 12-09-2015 26000 1024500 968000

In above Expected column 6930 = Transaction_Qty of last Transaction_Date of ILITM = 72331,

2520 = 9450-6930

-6300 = 2520 - 8820

Similarly 16000 = Transaction_Qty of last Transaction_Date of ILITM = 72302,

1008500 = 1024500 - 16000,

992000 =  1008500 - 16500

In above Expected table in Expected column we need only ILITM and ILMCU wise value which summation should equal to Onhand_qty for each ILITM and ILMCU.

for example in above table for ILITM and ILMCU as 72331 and 11091 we have  ONHAND_QTY = 9450 ,so In Expected column require only 6930 and 2520 because 6930+2520 = 9450

similarly for 72302 and 11091 ONHAND_QTY  = 1024500 , So In Expected column require only 16000 and 1008500 because 16000+1008500 = 1024500.

6 Replies
MVP & Luminary

## Re: Logic implementation

This should do it:

```T1:
if(Previous(ILITM)=ILITM,#ONHAND_QTY-peek('CumTQ'),#Transaction_Qty ) as Expected,
if(Previous(ILITM)=ILITM,rangesum(#Transaction_Qty,peek('CumTQ')),#Transaction_Qty) as CumTQ
INLINE [
ILITM, ILMCU, Transaction_Date, #Transaction_Qty, #ONHAND_QTY
72331, 11091, 15-09-2015, 6930, 9450
72331, 11091, 07-08-2015, 8820, 9450
72331, 11091, 23-05-2015, 8800, 9450
72302, 11091, 12-09-2015, 16000, 1024500
72302, 11091, 12-09-2015, 16500, 1024500
72302, 11091, 12-09-2015, 24000, 1024500
72302, 11091, 12-09-2015, 26000, 1024500
];
```

talk is cheap, supply exceeds demand
Contributor

## Re: Logic implementation

Hi Gysbert,

Thank you so much for your quick response this logic is working fine but how to apply limitation on expected column means Client want only those Transaction_Qty which summation will equal to Onhand_QTY.

as in below Image I need the data excluding highlighted rows.

MVP & Luminary

## Re: Logic implementation

 only those Transaction_Qty which summation will equal to Onhand_QTY

Can you explain that with some examples?

talk is cheap, supply exceeds demand
Contributor

## Re: Logic implementation

Hi Gysbert,

Here is example

ITM,     MCU, Trans_Date , TrnsQty, Stock

72331, 11091, 15-09-2015, 6930, 9450

72331, 11091, 07-08-2015, 8820, 9450

72331, 11091, 23-05-2015, 8800, 9450

in above table for same ITM and MCU we have three transaction 6930,8820 & 8800 but we have as on stock 9450,

so we want only transaction of 9450 means we will subtract stock - TrnsQty and stop subtraction when sum of       'stock - TrnsQty' = Stock

e.g  9450- 6930 = 2520, 2520-8820= -6300 then we will not consider -6300 because 6930 + 2520 = 9450

MVP & Luminary

## Re: Logic implementation

So you always want only the first two rows per ITM-MCU. Well, just add a row count and then remove what you don't need.

```T1:
if(Previous(ILITM)=ILITM,#ONHAND_QTY-peek('CumTQ'),#Transaction_Qty ) as Expected,
if(Previous(ILITM)=ILITM,rangesum(#Transaction_Qty,peek('CumTQ')),#Transaction_Qty) as CumTQ,
AutoNumber(RecNo(), ILITM & '|' & ILMCU) As RowNo
INLINE [
ILITM, ILMCU, Transaction_Date, #Transaction_Qty, #ONHAND_QTY
72331, 11091, 15-09-2015, 6930, 9450
72331, 11091, 07-08-2015, 8820, 9450
72331, 11091, 23-05-2015, 8800, 9450
72302, 11091, 12-09-2015, 16000, 1024500
72302, 11091, 12-09-2015, 16500, 1024500
72302, 11091, 12-09-2015, 24000, 1024500
72302, 11091, 12-09-2015, 26000, 1024500
];
```

talk is cheap, supply exceeds demand
Highlighted
Contributor

## Re: Logic implementation

Hi Gysbert,

First of all thanks a lot for your response , there is no of rows can be increase it will depend on the stock value if stock value adjust in two rows of transaction then it should stop there otherwise it will check until the stock value would not be equal to transaction qty.