Filter to show previous data

[zahidrahim_ocp]

Dear Experts,

I have a table with quantites like below;

Activity:

Item         Date                            quantity

1               01-SEP-2017                      10

1                02-SEP-2017                       -5

1               06-SEP-2017                         0

2               01-SEP-2017                         20

2                05-SEP-2017                        10

Now without dates it is showing balances using set expression like below:

1               5

2               30

if i select date 01-SEP-2017 it shows

1               10

2                20

No problem so far.

if i select filter 05-SEP-2017 it stop showing item:1 as there is no activity in item:1 on 05-SEP-2017

BUT I want to it keep showing all items with balances till that date.

  What should be the approach.

Regards,

Zahid Rahim

[florentina_doga]

see attach pls

[sebastianlettner]

Hallo,

you can use this expression

Sum({<Date={"<=$(=Max(Date))"}>}quantity)

make sure, that Date is a date (dual) field

Regards

Sebastian

[florentina_doga]

or maybe

[zahidrahim_ocp]

Dear Florentina,

Thank you for the solution. I am using below mentioned script as per your advice my table has more than 1 Crore rows and i am using Qlik Sense Desktop Edition with 8 GB of Ram. After pressing the load data the system remained hang for more than 2 hours and then script error out.

[mmt]:

LOAD TRANSACTION_ID,

INVENTORY_ITEM_ID&'~'&ORGANIZATION_ID AS ITEM_ID,

ORGANIZATION_ID,

LOCATOR_ID,

SUBINVENTORY_CODE,

Date(Floor(TRANSACTION_DATE)) AS TRANSACTION_DATE,

PRIMARY_QUANTITY

FROM [LIB://QVD/mtl_material_transactions.qvd] (qvd);

mtl_material_transactions:

load Distinct TRANSACTION_DATE AS TRANSACTION_DATE resident mmt;

LEFT Join (mtl_material_transactions)

LOAD ITEM_ID resident mmt;

LEFT Join (mtl_material_transactions)

LOAD TRANSACTION_ID,

ITEM_ID,

ORGANIZATION_ID,

LOCATOR_ID,

SUBINVENTORY_CODE,

TRANSACTION_DATE,

PRIMARY_QUANTITY

resident mmt;

Can we use any other approach.

Regards,

Zahid Rahim