Stock management - How to extract a stock value from a maximum date.

[#1]

Hello.

I am working on a stock management for my industry, and I need to know what is the value associated to a period, or to a dimension.

For example, in attacher file, I have got a short list a value.

For exemple, I need to know what is the stock in the end of September. It should be 809.1 t

BUSINESS UNIT	STOCK DATE TIME	MONTH	STOCK (tons)	CYCLE OF PRODUCTION
BUSINESS UNIT 1	03/10/2011 12:00	OCTOBER 2011	741,4	BR553S
BUSINESS UNIT 1	02/10/2011 12:00	OCTOBER 2011	776,4	BR553S
BUSINESS UNIT 1	02/10/2011 03:00	OCTOBER 2011	789,2	BR553S
BUSINESS UNIT 1	30/09/2011 07:30	SEPTEMBER 2011	809,1	BR553S
BUSINESS UNIT 1	29/09/2011 12:00	SEPTEMBER 2011	835,1	BR553S

I have to produce a dashboard like that, but I don't manage to create the expression.

MAY 2011	JUNE 2011	JULY 2011	AUGUST 2011	SEPTEMBER 2011	OCTOBER 2011
BUSINESS UNIT 1	468,4	877,1	724,5	216,9	809,1	741,4
BUSINESS UNIT 2	7855,2	5687,6	10048	7764,4	9789	9789

Is somebody can help me ?

Best regards.

[#2]

Hello,

if i had to solve this problem i would first try to get 3 new "dynamic dimension" out of the "STOCK DATE TIME" - Dimension.

Therefore you can use the "MID" - command

(plz check the QV-Help-Index.)

Then i would create a expression with if-clauses in combination with the "MAX" command.

i hope your one step closer to your solution.

[#3]

hi,

here is the attach qvw file which is the solution to your problem.

[#4]

Thank you.

Now, I am able to know the maximum date of a month.

But I try to find the maximum date for an association BUSINESS UNIT AND MONTH.

And then, the stock value associated with this date and business unit.

The solution should be on this way.

[#5]

i have given you all the stuff what you want.....in the second sheet i have provided you all the maximum date of the month....you just have to select MONTH field and the stock associated with the last date of that month comes in the text object.....check it once again.

[#6]

Ok. But I have to create a specific dashboard, including all the value of stock for the maximum date of each month, and for each business unit. In facts, I have 400 BU.

Can you produce a dashboard like this one:

I don't manage myself.

MAY 2011	JUNE 2011	JULY 2011	AUGUST 2011	SEPTEMBER 2011	OCTOBER 2011
BUSINESS UNIT 1	468,4	877,1	724,5	216,9	809,1	741,4
BUSINESS UNIT 2	7855,2	5687,6	10048	7764,4	9789	9789

Best regards.

[#7]

Hi, You can create the another with BU, MONTH and MONTHEND_STOCK in the script and just create the Pivot table on the dashboard. Please find the below script.

STOCK:

LOAD * ,

        AutoNumber([BUSINESS UNIT]&MONTH) AS KEY_BU_MONTH,

             AutoNumber([BUSINESS UNIT]&MONTH&[STOCK DATE TIME]) AS TEMP_KEY

;            

LOAD [BUSINESS UNIT],

     [STOCK DATE TIME],

     MONTH,

     [STOCK (tons)],

     [CYCLE OF PRODUCTION]

FROM [Stock management.xls] (biff, embedded labels, header is 1 lines, table is [Sheet1$]);

TEMP:

LOAD [BUSINESS UNIT], MONTH , MAX( [STOCK DATE TIME]) AS  [MAX STOCK DATE TIME]

Resident STOCK Group BY [BUSINESS UNIT], MONTH;

MAX_STOCK:

LOAD  AutoNumber([BUSINESS UNIT]&MONTH) AS KEY_BU_MONTH,  AutoNumber([BUSINESS UNIT]&MONTH& [MAX STOCK DATE TIME]) AS TEMP_KEY

Resident TEMP;

LEFT JOIN (MAX_STOCK)

LOAD TEMP_KEY, [STOCK (tons)] AS MONTHEND_STOCK

Resident STOCK;

DROP Field TEMP_KEY;

DROP Table TEMP;