Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Partner
Partner

Filter to show previous data

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

Tags (1)
4 Replies
Partner
Partner

Re: Filter to show previous data

see attach pls

Re: Filter to show previous data

Hallo,

you can use this expression

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

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

Regards

Sebastian

Partner
Partner

Re: Filter to show previous data

or maybe

Partner
Partner

Re: Filter to show previous data

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