Counter for Quantity greater than zero

[#1]

Hi All,

i have one issue on JDE database may one if you can help me...!!

i have table look like following:

Load

ItemNumber,

CostCenter,

Year,

JanQTY,

FebQTY,

MarQTY,

AprQTY,

MayQTY,

.

.

.

DecQTY,

From ItemHistory;

know i need make counter for each item in how many month sold...and ignore cost center ....

ItemNumber CostCenter JanQTY FebQTY MarQTY AprQTY

X I-21 0 200 0 10

X I-11 10 20 0 5

X I-22 0 0 0 10

Y I-22 19 0 0 0

-----------------------------------------------------------------------------------------------------------------

Result -X 1 1 0 1 TOTAL--> 3 Month

Result-Y 1 0 0 0 TOTAL--> 1 Month

by this i should know the total month have QTY on Year from 1 to 12 only,,,,, and ignore the cost center.

Any suggestion please help me while im stuck on this.....[:(]

[hector]

Hi, I guess this is a temp solution, it depends about the data and format

• Object: Straight/Pivot Table
• Dimension(s): ItemNumber
• Expression(s): 13 Expressions, 1-12 for every month in this format max(JanQTY)/max(JanQTY)
• The 13rd expression will be rangecount(column(1),column(2),column(3),column(4)....... column(12))

Explanation: If you divide every max value per item by itself, is going to be 1 if there is information, otherwise is going to be null, because is going to calculate 0/0, so the 12 expressions will be 1 o null, then with the rangecount() you will count only NON NULL value.

Anyway check the file that i've uploaded

rgds

[Oleg_Troyansky]

I'd recommend reading the 12 months using CROSSTABLE and generating 2 fields - Month and Quantity. Then, it's easier to operate - count months, summarize quantities, etc...