Re: Report on Aging Item in Stock - Qlik Community

oseimuduame · ‎2016-09-01

Good Evening,

i have a report i am trying to run on my qlikview which i need it to show me the following;

1. Items that have spend more than 30days in stock but not up to 60days (>30 <60)

2. Items that have spend more than 60days in stock but not up to 90days (>60 <90)

3. Items that have spend more than 90days in stock but not up to 120days (>90 <120)

Please how do i go about this kind of report.]

Thank you

sunny_talwar · ‎2016-09-01

Check if this link is helpful:

Buckets

oseimuduame · ‎2016-09-01

Thanks for the Post but you know this are numbers. How do i work with Dates and i need it also in a List not a bar chart.

i want something like a name of the item, the category it falls be it >30< 60 or anyone,

also the counts of that item for all the items that fall into the category.

The Reason for this is that i want to know items that have stayed for a very long time in my Inventory.

Please i need heelp

sunny_talwar · ‎2016-09-01

These ranges are based on a comparison to a particular date right? You can calculated day from two dates by subtracting one from the other. Ones you have days, you are all set, right?

EndDate - StartDate -> Number of Days

oseimuduame · ‎2016-09-01

thanks for the quick insight.

that should work. lemme try.

but how will i now make them satisfy the conditions below

1. Items that have spend more than 30days in stock but not up to 60days (>30 <60)

2. Items that have spend more than 60days in stock but not up to 90days (>60 <90)

3. Items that have spend more than 90days in stock but not up to 120days (>90 <120)

sunny_talwar · ‎2016-09-01

For those you need to look at the link I provided above

Buckets

oseimuduame · ‎2016-09-01

am i implementing the bucket in my script or where, cos i did and it was giving me an error

or is it going to be in my EXPrESSion?

sunny_talwar · ‎2016-09-01

It would be best to do in the script. Can you share the script so that I can give you direction

oseimuduame · ‎2016-09-01

//items

SQL SELECT

TO_CHAR(n.ITEM_SID) AS "ITEM_SID",

n.ITEM_NO AS "ITEM_NO",

n.SBS_NO AS "SBS_NO",

n.ACTIVE AS "ITEM_ACTIVE",

n.VEND_CODE AS "VEND_CODE",

n.DCS_CODE AS "DCS_CODE",

n.DESCRIPTION1 AS "ITEM_DESCRIPTION1",

n.DESCRIPTION2 AS "ITEM_DESCRIPTION2",

n.DESCRIPTION3 AS "ITEM_DESCRIPTION3",

n.DESCRIPTION4 AS "ITEM_DESCRIPTION4",

n.ATTR AS "ITEM_ATTR",

n.SIZ AS "ITEM_SIZ",

n.ALU AS "ITEM_ALU",

u.UPC AS "ITEM_UPC",

n.UDF1_DATE AS "UDF1_DATE",

n.UDF2_VALUE AS "UDF2_VALUE",

v.VEND_NAME AS "VEND_NAME",

v.FIRST_NAME AS "VEND_FIRST_NAME",

v.LAST_NAME AS "VEND_LAST_NAME",

SUBSTR(d.DCS_CODE,1,3) AS "D_CODE",

SUBSTR(d.DCS_CODE,4,3) AS "C_CODE",

SUBSTR(d.DCS_CODE,7,3) AS "S_CODE",

d.D_NAME AS "D_NAME",

d.C_NAME AS "C_NAME",

d.S_NAME AS "S_NAME",

d.D_LONG_NAME AS "D_LONG_NAME",

d.C_LONG_NAME AS "C_LONG_NAME",

d.S_LONG_NAME AS "S_LONG_NAME",

d.ACTIVE AS "DCS_ACTIVE",

n.COST AS "ITEM_COST",

TO_CHAR(n.FST_RCVD_DATE, 'DD-MM-YYYY') AS "FIRST_RCVD_DATE",

TO_CHAR(n.FST_RCVD_DATE, 'YYYY') AS "FIRST_RCVD_YEAR",

TO_CHAR(n.FST_RCVD_DATE, 'MM') AS "FIRST_RCVD_MONTH",

TO_CHAR(n.FST_RCVD_DATE, 'DD') AS "FIRST_RCVD_DAY",

TO_CHAR(n.LST_RCVD_DATE, 'DD-MM-YYYY') AS "LAST_RCVD_DATE",

TO_CHAR(n.LST_RCVD_DATE, 'YYYY') AS "LAST_RCVD_YEAR",

TO_CHAR(n.LST_RCVD_DATE, 'MM') AS "LAST_RCVD_MONTH",

TO_CHAR(n.LST_RCVD_DATE, 'DD') AS "LAST_RCVD_DAY"

FROM INVN_SBS n

LEFT JOIN INVENTORY u ON n.ITEM_SID = u.ITEM_SID

LEFT JOIN VENDOR v ON v.VEND_CODE = n.VEND_CODE AND v.SBS_NO = n.SBS_NO

LEFT JOIN DCS d ON d.DCS_CODE = n.DCS_CODE AND d.SBS_NO = n.SBS_NO

WHERE n.SBS_NO in $(subsidiaries);

My last received date = LAST_RCVD_DATE

All i want is so that i can have > 30days < 60 and >60<90 also >90<120

i would really appreciate

lemme see if i can share you my sample also

sunny_talwar · ‎2016-09-01

Are you looking for, 30 day or 60 day from Today()?