Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello
I have an expression that I am using in table (see word document)
=round(sum({
<period ={">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, pl -={"X01*"}, bussline_abrev = {PE}
>}[Investment GBP]))
It shows Office Location, Portfolio and Value
I will collapse the Portfolio, all I want to see is Office Location plus the number of portfolios in each company. I assume this has something to do with the Count function. Eg Benelux wold show 6 and France 1 and so on
How about this?
=Count(DISTINCT {<period ={">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, pl -={"X01*"}, bussline_abrev = {PE}>} portfolio)
Not sure if portfolio is the name of the field or if it is just a label, but make sure to use the name of your second field in the table from the 1st screenshot
So, when expanded, you want to see the sum of value and when collapsed, you want to see count??
Hi
Here is the table I am trying to copy
How about this?
=Count(DISTINCT {<period ={">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, pl -={"X01*"}, bussline_abrev = {PE}>} portfolio)
Not sure if portfolio is the name of the field or if it is just a label, but make sure to use the name of your second field in the table from the 1st screenshot
great thanks
I amended it slightly, so to only sure where values > 0 and then did dimension limits to restrict where values > 0
But many thanks for your help, it is appreciated
If(
round(sum(
{
<period ={">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, pl -={"X01*"}, bussline_abrev = {PE}
>
}
[Investment GBP])
)
>0,
Count(DISTINCT {<period ={">=$(vStartPeriod_CY)<=$(vEndPeriod_CY)"}, pl -={"X01*"}, bussline_abrev = {PE}>} portfolio), '')