6 Replies Latest reply on Jan 24, 2014 5:08 AM by Prashant Sharma

# Inventory metric

I am trying to create a measure that is linked to a dimension.  For example: I have a measure (inventory on hand) that can not be summed over time so in week 1 i have 10 units week 2 i have 5 units week 3 i have 17 units and week 4 i have 6.  If i was to look at this measure for a total month then the total would = 6 or the max week in that month.

My question is there a way to create a measure that something like if Month 1 is selected the Inventory Measure = the max week of the month?  and at the same time have a measure like sales that will still be summed over time?

I would like our users to be able to use a quick filter on months and depending on what month they select it would show the inventory for the maximum week available for that month.

• ###### 1. Re: Inventory metric

Hi Justin,

Please attach a sample workbook (.twbx). BTW, Create a calculated field using formula: -

max(datepart('week',[Order Date],'sunday'))

Replace [Order Date] with your date field. Here, 'Sunday' is the starting day of your week. Now, use this date as Quick filter on Month. Hope this may help you!

Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 2. Re: Re: Inventory metric

Because i dont know how to actually create the measure in tableau i created this workbook as an example:

I have Months, Weeks, Sales and Inventory.  At the very end you can see the "Total" for Sales is equal to the sum of all weeks where as Inventory is only equal to the last completed week.

The Actual goal would be to put months a quick filter then based on what ever is selected in the quick filter would only select the last completed week for Inventory but still be able to sum the Sales for that month selected.

• ###### 3. Re: Re: Re: Inventory metric

Hi Justin,

I have created two sheets & put those two in a single dashboard. Check Dashboard. This is the simplest method to modify the Grand Total according to you. Hope this may help you!

Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 4. Re: Re: Inventory metric

That solution worked for me: thank you!

• ###### 5. Re: Re: Inventory metric

This is getting closer to what I am looking for, the problem is i may have month as a Quick Filter/Parameter but i wont necessarily have it in the rows/columns.  So say im looking at 4 different stores (Retail) and i want to see what the inventory is for a month can I still use this window(Max) function?

I am having trouble applying it to my work book i can post another workbook once i have built it in the format I am trying to get at