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

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.

• ###### 1. Re: Show data only for the latest month irrespective of time period selection

I could not open your file , So

Try This for

[New_M_Fee]

{FIXED CODE:AVG(IF {FIXED CODE:MAX(Month)}=Month THEN M_Fee END)} This Calculation wont Change unless you add date to CONTEXT

This is at Code Level, you can multiply it with the SUM(Qty) by Date to get what you need

In That case you need to use date dependent calc

[SUM at CODE LEVEL]

SUM(QTY)}

Drag Code to Rows and Create a  third Calculation as:

[New_M_Fee]*[SUM at CODE LEVEL]

Now I changed the Date in Filter See the Sum (Sales) at Max date wont change

See attached

Thanks

Deepak

• ###### 2. Re: Show data only for the latest month irrespective of time period selection

I am using 10.5 version. Please let me know if it can't open, i will share it again.

It works with the Global super store data, but doesn't work with my data. There are some calculations need to addressed on the fly at code level.

C_M_Fee: Is the expected data and shouldn't change with Month but should change with other filter.

Calculation 7: Data as per the logic provided by you, which is very close to it but not exactly.

Attached the data for your reference.

Note: I shouldn't include any Boolean etc in the filters pane as the results might effect for other measures.

Regards,

Vasu.

• ###### 3. Re: Show data only for the latest month irrespective of time period selection

Hello Experts,

I know this is very typical logic to be implemented. but wanted to check if there is any work around.

Thanks.

• ###### 4. Re: Show data only for the latest month irrespective of time period selection

Just wanted to check if any one has any work around? Will this be possible to do in Tableau?

Appreciate if someone would look into this.

• ###### 5. Re: Show data only for the latest month irrespective of time period selection

Do i have any good news? Please let me know if there is any work around.