10/9/16

Tableau- Real Time Scenario 5 (Showing inventory for selected day if not selected then show last day inventory by month wise)

Problem: Showing inventory for selected day, if not selected then show last day inventory by month wise.

See images below:

In the above image showing inventory for 15th of every month. Because user entered day as 15. suppose if user does not enter 15 and leave that as blank then it will show last day Inventory for every month. 


See below


Note: If you enter any thing apart from 1 to 31 then the report will become blank.

Solution:

Step 1: Create parameter “Day” with data type as string. See blow image

Step 2: Drag MonthDay columns to row shelf and right click on Day parameter select show parameter control.

Step 3: Create a calculated field as “Max or selected day” using below formula.

If [Parameters].[Day]='' then window_Max(max([Day]))
Else int([Parameters].[Day])
End

Note: In above formula '' is not double quote it is two single quotes side by side with out space in between.

Formula working in this way, suppose parameter [Parameters].[Day] is empty means no value entered by user then it will find the max day of each month other wise it will return user entered day valueAs we created parameter as string data type so we are converting that value to integer again in else statement.

Step 4: Drag this newly created calculated field “Max or selected day” to text marks shelf.

In above image you can observe that for month of February it showing max date as 31 which is wrong.
Because we used window_max it's calculating max day across table by default. Window_max is a table calculation.

Step 5: Now right click on “Max or selected day” which is there in text marks shelf and go toCompute using then select pane(down).

Now you will see max day for February as 28th.

Step 6: Create one more calculate field as “Max day filter” using following formula.

If attr(Day) = [Max or selected day] then "True"
Else "False"
END

Note: [Max or selected day] is an aggregated column which is created in step 3. [Day] is not an aggregated column. We cant mix aggregated and non aggregated columns in calculation. So I applied attr function for non aggregated [Day] column.

Step 7: Now drag [Max or selected day] column to filter shelf and select only “True”.

Step 8: Remove column “Max or selected day” from marks text shelf and add “Stock in Inventory” column to marks text shelf.

Finally the report looks like below. You test report with and without day value in parameter.


Please see the report below:


Google Q&A Forum