5 Replies Latest reply on Mar 10, 2018 4:26 PM by Vasu choudhury

    Show data only for the latest month irrespective of time period selection

    Vasu choudhury

      Experts,

      I am running out with some complex logic. Please let me know if there is any work around.

       

      Requirement:

      I have a time period(default is full range) and some quick filters in the dashboard.

      1. Display the M_Fee for the latest month(as per the data available) at code level and not at the time period. Even if i change the time period from the dashboard, M_Fee logic should not change, but can be changed at other dim levels.

      Lets say, C750 has max date as 10/1/2017 and C505 has 9/1/2017 data. I should pick the data from max(month) at each code level and not at the column level. Since C505 doesnt have 10/1/2017, then i should not ignore  this code, instead i need to look for the 9/1/2017 and show the data and so on for rest of the codes.

      2. Need to take an average if i have more no of rows for the same dim values(Highlighted in Green color). There are some rules to keep it same instead of aggregation. In this case I need to do an Average of M_Fee at each code level for the max(month)

      Note: I will be having same M_Fee for the same month & code level in the entire table.

      3. SUM(Qty) should be calculated at time period selection level and other quick filter selection and not by the max(month)

      Lets say time period selected is 12/1/2016 to 10/1/2017 then QTY for C750 is 1497; for 2/1/2017 to 17/1/2017 then QTY for C750 is 788 and so on

      4. Final output is M_Fee( max(month) at code level ) multiply by SUM(QTY) at time period

      Note: All the above logic should satisfy for other quick filter selections.

       

      Hope it is clear and let me know if it is doable in Tableau. Attached the workbook (10.5)

       

      I am very close to it..but don't know where I am going wrong.

      Note: I need context filters to calculate for other measures.

       

      Thanks,

      Vasu.