Jump to content

How to calculate an average on a rolled-up cross table linked to a tree map filter selector


Zeno Lin

Recommended Posts

Data

 

 

 

Date

patient days

days in month

Patient days per day

 

 

 

1/2/2018

11

31

0.3548

 

 

 

1/5/2018

20

31

0.645161

 

 

 

2/22/2018

15

28

0.53571

 

 

 

2/25/2018

8

28

 

0.2857

 

 

 

 

 

 

 

 

 

 

 

Cross Table 1

 

 

 

Month

Sum of Patient Days

Avg (Days in Month)

 

Avg Daily Patient days

(sum of Patient days per day)

 

 

 

 

Jan

31

31

1

 

 

Feb

23

28

0.8214

 

 

 

Cross Table 2:

 

 

 

Year

Sum of Patient Days

Days in months selected

 

Avg Daily Patient days

 

 

 

2018

54

should = 59

should = 0.915

 

 

 

 

 

 

 

 

 

Crosstable 2 is linked to a month selectiontree map

How do I get Cross table 2 to show 59 and 0.915 in columns 3 and 4 perspectively

If the data has more years and more months, how to make columns 3 and 4 calculate correctly based on the months selected by the treemap filter buttons

Link to comment
Share on other sites

  • 1 month later...

for 3rd column, you can follow below article to extract unique value of days for each month (ignoring duplicate values)and then sum those up.

https://support.tibco.com/s/article/Tibco-KnowledgeArticle-Article-42284

Sum(if(Rank(baserowid(),"asc",[Month])=1,[Value],Null))4th column expression should be straight forward once you have column 3rd expression ready:

Sum([patient days])/Sum(if(Rank(baserowid(),"asc",[Month])=1,[Value],Null))

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...