Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
see attach pls
Hallo,
you can use this expression
Sum({<Date={"<=$(=Max(Date))"}>}quantity)
make sure, that Date is a date (dual) field
Regards
Sebastian
or maybe
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