topic Re: Filter to show previous data in QlikView

Filter to show previous data

zahidrahim_ocp — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Filter to show previous data

florentina_doga — Fri, 15 Sep 2017 07:31:59 GMT

see attach pls

Re: Filter to show previous data

sebastianlettner — Fri, 15 Sep 2017 08:30:20 GMT

Hallo,

you can use this expression

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

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

Regards

Sebastian

Re: Filter to show previous data

florentina_doga — Fri, 15 Sep 2017 09:28:18 GMT

or maybe

Re: Filter to show previous data

zahidrahim_ocp — Fri, 15 Sep 2017 11:19:02 GMT

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