8 Replies Latest reply on Feb 7, 2017 9:24 AM by Vishal D

# Dynamic Calculated field based on Year - Month (Expand Collapse Hierarchy)

I have below 3 fields coming from database HANA

[Year] = [2016,2015,2014,2013]

[Month] = [1,2,3,4,5,6,7,8,9,10,11,12]

[Sales] = [123,654,789,234,......]

I want to show Sales by month bar chart with expand collapse capability so I created hierarchy Year->Month. This works well.

Issue is on tool tip I want to show Avg Sales Day which is Sales/# of days in either month or year bases on what is shown on chart.

Initially I want all Years bar chart so Avg Sales day = Sales/# of day in each year but when the user click expand now we are showing chart by month of each year so now the tool tip should show Avg Sales Day = Sales/# of day in each month of a year

So how do I dynamically calculate this Avg per Day based on where I have yearly chart of monthly chart when user clicks on expand?

My current formulas :-

# of days in Year =

DATEDIFF('day',date(STR([Report Year])+'-01-01'),

IF ([Report Year]=DATEPART('year',NOW()))

THEN NOW()ELSE DATE(STR([Report Year])+'-12-31')end)+1

# of days in Month =

datediff('day',date(str([Report Year])

+ '-' + str([Report Month]) + '-01'),

(IF(INT([Report Month])= DATEPART('month',NOW())

AND(INT([Report Year])= DATEPART('year',NOW())))

THEN DATE(str([Report Year])

+ '-' + str([Report Month]) + '-'+ STR(DATEPART('day',NOW())))

+ '-' + str([Report Month]) + '-01'))END))

Avg Monthly Sales per Day = IFNULL([Sales],0)/[# of days in Month]

Avg Yearly Sales per day = IFNULL([Sales],0)/[# of days in Year]

• ###### 1. Re: Dynamic Calculated field based on Year - Month (Expand Collapse Hierarchy)

Anyone?

• ###### 2. Re: Dynamic Calculated field based on Year - Month (Expand Collapse Hierarchy)

So how do I dynamically calculate this Avg per Day based on whether I have yearly chart or monthly chart based on when user clicks on expand?

• ###### 3. Re: Dynamic Calculated field based on Year - Month (Expand Collapse Hierarchy)

Hi Vishal;

This is similar to a question I posed to the Tableau Doctors at TC2016 regarding how to determine what level of a hierarchy was active. It proved extremely difficult!

And while I love Tableau's hierarchy function, I've found this simple inquiry to be almost more trouble than what it is worth (feedback, anyone?).

Before I walk you through the steps I was able to use, could I suggest a slightly different (and possibly sanity-saving) route?

What if you used a parameter to control your view level?

Key calculations (and even titles) would be dependent on this parameter, changing their values appropriately. Example:

IF [View Parameter] = "Year" THEN ABC

ELSE XYZ

END

If you're dead-set on using dynamic hierarchies functions in Tableau, I can share the work I've done

• ###### 4. Re: Dynamic Calculated field based on Year - Month (Expand Collapse Hierarchy)

I think I can use parameter approach for my current requirement but I would still like to understand how do you capture the level of hierarchy clicked?

• ###### 5. Re: Dynamic Calculated field based on Year - Month (Expand Collapse Hierarchy)

Hello Vishal; If you just have two levels, this is may be fairly easy.

Let's say we have two levels: Level A and Level B

I created a calculation:

Level A Count

{exclude [Level B]: countd([Level A])}

And then a second calculation:

Active Level

if [Records] = attr([Level A Count]) then "Level A is Active"

else "Level B is Active"

END

As the user expands the levels, the [Active Level] value changes as well.

If this doesn't work with dates, you may be able to break them into a hierarchy using the Datepart. Let me know!

• ###### 6. Re: Dynamic Calculated field based on Year - Month (Expand Collapse Hierarchy)

What is [Records] - do you mean # of records?

• ###### 7. Re: Dynamic Calculated field based on Year - Month (Expand Collapse Hierarchy)

Apologies-- [Records] is a WINDOWS_COUNT...

This marks the first time I've used that function!

• ###### 8. Re: Dynamic Calculated field based on Year - Month (Expand Collapse Hierarchy)

Michael Hesser

Yes I have two level hierarchy

Report Year (Level A)

- Report Month (Level B)

But for some reason I am still not getting the right active level captured.

Attached twbx