Aggregating "Ledig i hele perioden" for "Boligenheder"
I have a cross-tabulation involving "Boligenheder," "Boliger," and "Ledighed" (Vacancy). I am trying to create a formula to determine if a residence has been vacant for the entire period. Unfortunately, the current formula is not working as expected when viewing the aggregation for "Boligenheder." It seems to only work for the individual residences.
Here is the formula I've used for "Ledig i hele perioden":
if (sum(c2, 0, m2) = sum(c1, 0, m2)) then 1 else 0
Where C1 represents "Dage i alt" (Total Days) and C2 represents "Ledig tid" (Vacancy Period). The formula correctly assigns 1 or 0 for each residence, but when I look at the summation for "Boligenheder," which includes all residences under it, the total is always 0. This is despite the possibility of having two residences under a "Boligenhed" that have been vacant for the entire period. How can I correctly aggregate the sum for "Boligenheder" for the variable "Ledig i hele perioden"?
Thanks in advance - Lars Kjær
Comments
Hi Lars
I'm not entirely sure what you think is the correct result on aggregated level.
Right now I guess it shows if the whole "Boligenhed" has been vacant.
Maybe you want it to show that at least one address has been vacant in the entire period?
Or maybe the right answer is a fraction showing how large a proportion of the individual addresses has been vacant in the entire period?
Both can be solved with an extra calculation (and then hiding your "Ledig hele perioden" calculation).
One idea could be another calculated column with a syntax like this (c3 is your "Ledig hele perioden"):
If allcount(c3,all(c),m1)>0 then max(c3,all(c),m1) else sum(c3,0,m1)
The allcount tests if there are any "children" - meaning if we are looking at a subtotal (aggregated level). If that is the case it retrieves the max value of all the children (either 1 or 0).
This formula will show 1 if any of the addresses have been vacant during the entire period.
Another version will do an average of the "children":
If allcount(c3,all(c),m1)>0 then avg(c3,all(c),m1) else sum(c3,0,m1)
This version will come up with a fraction that says how big a proportion of the individual addresses have been vacant the entire period.
Or maybe that't not quite what you are looking for?
br/Niels
Hi Niels. Thank you very much for your prompt response. To elaborate: In the first picture, there is a black ring around "0" next to Enebærvej.... On Enebærvej, both Enebærvej 32 and Enebærvej 6 have been vacant throughout the entire period. Therefore, I would like a "2" to appear in the black ring under the total for Enebærvej. Similarly, there is actually one for "Område Nord" and the Overall Total. I haven't mentioned these, but in the same way, I wanted to summarize how many homes in "Område Nord" and in the overall total have been vacant throughout the period. Does that make sense?
I have inserted your two suggestions as "Formula 1" and "Formula 2". "Formula 1" does not seem to count correctly for the housing unit. Under Enebærvej, it shows "1", but there are 2 vacant homes. "Formula 2" seems to count correctly up to the overall total, where it shows "0.00".
ok - a slight change to formula 1:
If allcount(c3,all(c),m1)>0 then sum(c3,all(c),m1) else sum(c3,0,m1)
Now it should sum up all the "children" and give you the number of vacant homes.
However to "roll up" the overall total - you will need to do another calculated column like this:
If allcount(c4,all(c),m1)>0 then sum(c4,all(c),m1) else sum(c4,0,m1)
If you go with formula 2, I'm not quite sure what you want the grand total to be.
I have inserted the new formula as "Formula 3". It appears to count correctly for individual homes, the housing unit, and the area. However, the overall total is not correct; it shows "2".
In comparison to "Formula 2" and the overall total: The ratio on the overall total should be "Antallet af ledige boliger i hele perioden" / "Antal boliger" - this ratio could be applied to both the home, the housing unit, the area, and the overall total.
It would be great if "Formula 2" could display this. The ratio is a good benchmark.
ok - I missed that there is another level of totals. You can only roll them up one level at a time.
If you do another column you can finish it. It would be something like:
If allcount(c5,all(c),m1)>0 then sum(c5,all(c),m1) else sum(c5,0,m1).
Now we will have a number showing how many vacant homes there are at each level.
To be able to calculate the ratio, we need the number of homes on all levels as well - I think that we are in luck, cause that's the "Antal Boliger" as far as I can see.
So assuming you did the previous calculation - we now have the correct number of vacant homes called c6 - so the ratio would be fairly simple:
sum(c6,0,m1)/sum(d1,0,m1)
Maybe c6 will be zero sometimes, so you could add an if sentence:
if sum(c6,0,m1) = 0 then 0 else sum(c6,0,m1)/sum(d1,0,m1)
Hi again.
Formel 4 = If allcount(c5,all(c),m1)>0 then sum(c5,all(c),m1) else sum(c5,0,m1)
Formel 5 = if sum(c6,0,m1) = 0 then 0 else sum(c6,0,m1)/sum(d1,0,m1)
Unfortunately, it seems that Formula 4 counts correctly for Home and Housing Unit, but not for Area and Total. Formula 5 counts correctly except for the total.
I think there is some confusion about the referencing.
In the beginning, you created this formula as a column:
if (sum(c2, 0, m2) = sum(c1, 0, m2)) then 1 else 0
This is now c3.
So now we roll up to the lowest aggregation level by writing:
If allcount(c3,all(c),m1)>0 then sum(c3,all(c),m1) else sum(c3,0,m1)
This fixes the aggregated level on Boligenhed and is now c4.
If allcount(c4,all(c),m1)>0 then sum(c4,all(c),m1) else sum(c4,0,m1)
This fixes the aggregated level on Område and is now c5.
If allcount(c5,all(c),m1)>0 then sum(c5,all(c),m1) else sum(c5,0,m1)
This fixes the aggregated level on Grand total and is now c6.
The last calculated column shows the number of vacant houses on all levels correctly, I hope.
Now we are ready for the ratio:
if sum(c6,0,m1) = 0 then 0 else sum(c6,0,m1)/sum(d1,0,m1)
And now you need to hide the intermediates - and hopefully that's it.
A thousand thanks. Now I understand, and it works exactly as it should. Thank you for the quick assistance and your patience :-)
No worries - great that it worked :-)
Please sign in to leave a comment.