COUNTD requires Tableau to look at the data on a row-by-row basis, but a blend can only see an aggregate.
I'll take a look at your workbook and see if I can recommend something.
I made a FIXED LOD in the secondary source that I could change into a dimension. Now the evaluation happens before the blend occurs, and you have access to that value. I think this is getting you what you need.
Note that when you initially drag this onto your Sheet 9, it will go on as a DISCRETE pill (blue pill) so when you use it on the colors shelf you get a whole bunch of different colors rather than a spectrum of blue shades. But I edited the pill to change it from discrete to continuous, and that gives the dark-to-light shading.
Classeur3_v18.1.twbx 3.0 MB
thanks for your help.
I think i understand what you did.
It's nearly what i'm looking for. I tried, but I also want the number into tooltip.
I also have checked the result, and it doesnt seems to be correct. For exemple, for year 2014 and district 'ZONE NATURELLE' in my base i found 22 distinct ID_FAM, on the map it's 40.
I just created Calc 1 at the level of [Quarter]. If you want individual year values, just add that to the LOD.
In the attached I modified Calc1 to compute at the level of quarter and year. I made Sheet 2 to display the values.
Then I tried to use it on Sheet 9. Well, that compartmentalizes the map at both [Name] and Year. And if I did SUM(Calculation1]) I got the same error as you were getting before.
So I made Calculation2. This is another LOD to sum up the years for each [Quarter].
Now I can use that on Sheet9.
The point is to do row-level work in the secondary source, so that when the blend pulls data it doesn't have to do the row-level work.
Also, in the attached I have added both Calc1 and Calc2 to the tooltip in Sheet 9. Notice that Calc1 gives you an asterisk if there are multiple values returned from the secondary source. Change the year filter to grab just 2014 and you'll get 22 in ZONE NATURELLE for calc1, for example. Keep in mind that no matter what years you select here, calc2 will always be 205 for Zone Naturelle. That's because Tableau is doing the work in all the data of the secondary source before pulling anything into the sheet's underlying table.
Also, I'm not sure why the earlier version of this was getting 40 for Zone Naturelle. It should be 205, as shown on Sheet 2. The calcs are doing the right thing now.
And one final thing. If you want the map to be filtered by year from the secondary source, we'll have to do something different than just putting the secondary field on the filter shelf. The LOD isn't governed by that filter. (That's how FIXED LODs work.) Perhaps we can do a Start-year and an end-year parameter. You'll be able to control the fixed LODs that way.
Classeur3_v18.1 A.twbx 3.1 MB
yes, thanks, it seems to be right !
As all of my maps will turn around the district, i'll do the same for each variable ?
First, i'll try to understand what you did.
Yup. You'll have to do it for each variable -- for those that you want COUNTD. If you just want COUNT, this won't be necessary. Ditto SUM and other aggregates.
Last question, I tried to have nb_enf as filter for exemple, but nothing happens when i select nb_enf (what i'm looking for, it's the number of family by district with 1,2,or 3 child for exemple).
Have I to put also this variable into the LOD ? (i tried, but unsuccesfully ....)
In Tableau's order of operation, filters are evaluated AFTER a FIXED LOD is evaluated. On Sheet 2 I have added [Nb Enf] to the filters, and selected for 1,2,3. You can see that no matter what you do with that filter, the values on the sheet stay the same because they are computed by FIXED LODs.
Go to Sheet 2(2). Tableau has given us a way to force the filter to be evaluated BEFORE the FIXED LODs. By right clicking the filter and adding it to CONTEXT, the LOD gets evaluated AFTER the filter. Pretty nice. So you can have some filters in context, and some out of context, and control where those filters get evaluated in relation to FIXED LODs.
But that's not the end of it. On your sheet 9, you are using those LODs from the secondary source. We're not going to have values 1-through-13 there for SUM([Nb Enf]). (Put it on the sheet and see that you're getting the sum across each quartier, and therefore you get giant values for the field.)
You're going to have to make that evaluation happen in the FIXED LOD as you surmised.
On Sheet 2(3) I simulated what the NB ENF filter was doing on 2(2). I made a FIXED calc to capture the sum of [Nb Enf] for each row by evaluating the sum at the level of all the dimensions in BI_FAMILLE_H_2 (which is where [Nb Enf] is coming from.) See [Nb Enf to use]. Next look at [Calculation1 (copy)]. In there I do the COUNTD only if the value to use is 1, 2 or 3. You can see that no matter what you do with the filters, you get the same 209 value in the first slot that you do on Sheet 2(2) that was using the in-context filter.
Yes, this is a bit complicated. The need to use sums and countD values from the secondary source forces us to create all these FIXED LODs so that the value can be used at the dimension level in the blend.
Classeur3_v18.1 B.twbx 3.1 MB
Thank you again.
I think i start understanding how it works.