It would really help if you uploaded a sample workbook.
You want to do a WINDOW_SUM( whatever puts out 1,503,108 for Australia ) and have it restart every [Region].
And it's a lot easier to show you how to do that with your own example than to try to explain it conceptually.
So hack up an example that simulates what you have. You don't need all the countries (or even all the regions). Just a few of each. And you don't need real data. Just load it with bogus values. It's simple to do with an excel data source. Mock up the map you have now with the simple data set, and we can go from there.
Thanks for response..Please find the attached workbook with sample data.
we should show tool tip as:
Region : ASIAPAC
Country count for C1: 40
Overall Region Count: 90
If we hover over australia - we can get country count as 40 and Region count should be 90 ( not 100 if we add all countries in that region).
Note: Region count is near to the actual aggregate count for country but not the same
Tooltip_calculation.twbx 51.8 KB
(Note to self: V92. workbook.)
Sorry I didnot get your workbook file
Actually, I don't understand why you say it shouldn't be 100. Sheet2 says that Maldives is in ASIAPAC. In fact, sheet2 shows that there are 190 total in ASIAPAC because of the 90 in country=NULL.
In the attached I created a calc called Region Count. I put it on the data shelf, added it to the tooltips, and edited the table calc to cycle through the countries and restart every Region. (See Sheet 1).
On Sheet3 I made a copy of the calc, and added in a condition not to add "NULL" countries. Now you get 100.
If you want to exclude other things, you can modify the condition using the same sort of concept.
Tooltip_calculation 2.twbx 80.3 KB
Just a small clarification to your previous post, 90 in region = null but not 90 in country = null.
As i mentioned in my previous post country sumup in a particular region is not equal to region sumup for all the countries in that region, both counts vary a bit according to my actual data.
So in the sheet 3 toop tip we should get REgion count= 90 for ASIAPAC than 190
Any clew on how to get the above count plz. Appreciate your help as i am still a beginner to coding part.
Thanks, I got the code right for showing 90 count by modifying yours calc field..really thanks for taking your valuable time
The table calc addresses the table by dimensions. There is a dimension called [Region]. In Sheet 2 there are 5 rows under [Region]="ASIAPAC"
But now I see what you mean by Region as highlighted in your screen shot. It's a special row in the data, not a group of rows differentiated by a dimension.
You indicated that you got it worked out. You probably modified the calc to say, IF ATTR([Country Class] <> "Region" ...
But I still see 4 other rows on sheet 3 that have [Country Class]="Country", and the 4 values are 10,20,30,40, which add up to 100. (Australia, China, Maldives, Nepal.)
But yes, it looks like you can eliminate these rows that have [Country Class] = "Region" because you can derive them with the calc we have in this example. For that matter, you may be able also to eliminate the whole column [Country Class] if the only two values you have there are "Country" and "Region".