Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

MONTHSTOCK (tons)CYCLE OF PRODUCTION
BUSINESS UNIT 103/10/2011 12:00OCTOBER 2011741,4BR553S
BUSINESS UNIT 102/10/2011 12:00OCTOBER 2011776,4BR553S
BUSINESS UNIT 102/10/2011 03:00OCTOBER 2011789,2BR553S
BUSINESS UNIT 130/09/2011 07:30SEPTEMBER 2011809,1BR553S
BUSINESS UNIT 129/09/2011 12:00SEPTEMBER 2011835,1BR553S

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

MAY 2011JUNE 2011JULY 2011AUGUST 2011SEPTEMBER 2011OCTOBER 2011
BUSINESS UNIT 1468,4877,1724,5216,9809,1741,4
BUSINESS UNIT 27855,25687,6100487764,497899789

Is somebody can help me ?

Best regards.

6 Replies
Not applicable
Author

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.

Not applicable
Author

hi,

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

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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 2011JUNE 2011JULY 2011AUGUST 2011SEPTEMBER 2011OCTOBER 2011
BUSINESS UNIT 1468,4877,1724,5216,9809,1741,4
BUSINESS UNIT 27855,25687,6100487764,497899789

Best regards.

Not applicable
Author

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;