topic Counter for Quantity greater than zero in QlikView

Counter for Quantity greater than zero

Tue, 07 Sep 2010 18:50:04 GMT

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.....[:(]

Counter for Quantity greater than zero

hector — Tue, 07 Sep 2010 19:25:41 GMT

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

Counter for Quantity greater than zero

Oleg_Troyansky — Tue, 07 Sep 2010 19:50:51 GMT

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...