nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Little help with my report

Smakosh

Hello! It will be nice to someone help me with my report or give same ideas. I want get in the results 'Product name' and how many days his quantity=0. For example I have this table

So I want to see:

Period is not necessary.

PS. I dont want you do this for me, if you give me some tip it will be Okey.

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

logical column with a 1 or 0 based on the criteria of quantity by day ... then sum up the new logical column over time ...

Smakosh

Thanks Thomas I do like you say.

Did you know how I can do this with Period. I must write question like this "show me period, product, quantity, and how many when day -1 quantity of produt was 1 OR NULL".

For 0/1 column i do this formula:

SUM((CASE WHEN "Dane"."Quantity"=0 THEN 1 WHEN "Dane"."Quantity">0 THEN 0 WHEN "Dane"".Quantity)" IS NULL THEN 1 ELSE NULL END) BY "Product"."Product (name)"

Result:

Smakosh

mistake:

Did you know how I can do this with Period. I must write question like this "show me period, product, with quantity=0 or NULL, and how many days quantity was 0 or NULL when day -1 quantity of product was 1

Thomas Dodds

You are much better off 'architecting' a physical data structure that records the PRODUCT, DAY, QUANTITY, PREVIOUS DAY QUANTITY, LAST NON-ZERO DAY

Have a row for EVERY Product and Day ... now you have no nulls only zeros for appropriate day.

PREVIOUS DAY QUANTITY = LAG(QUANTITY) OVER(PARTITION BY PRODUCT ORDER BY DAY)

LAST NON-ZERO DAY = (SELECT MAX(DAY) FROM TABLE WHERE PRODUCT = x.PRODUCT and QUANTITY > 0)

^ a sub-select in the row will get you this

Smakosh

Thanks Thomas