11 Replies Latest reply: Mar 16, 2018 10:16 AM by Christian Eatman

# GetSelectedCount with sumif

Hi,

I am trying to display a KPI for a certain year if no selection is made in the year filter. Specifically, I want to show the sum of an amount for the year 2018 if no selection is made. It seems from my research that GetSelectedCount would allow me to do this. This is what I tried, but does not work:

sum(if(GetSelectedCount([Sheet1.Year]=0) and{<[Sheet1.Year]={2018}>}),Amount)

I have tried this expression in various forms (different placement of commas, different placement of parentheses, placing the sum in a different place), but nothing has worked. Is it possible to accomplish what I want using GetSelectedCount? If not, are there other ways to do this?

Thanks!

• ###### Re: GetSelectedCount with sumif

Try that

sum({<GetSelectedCount([Sheet1.Year]={'0'}), [Sheet1.Year]={'2018'}>}Amount)

if(GetSelectedCount([Sheet1.Year]=0,Sum({<[Sheet1.Year]={'2018'}>}Amount),Sum(Amount)

• ###### Re: GetSelectedCount with sumif

Try this

If(GetSelectedCount([Sheet1.Year]) = 0, Sum({<[Sheet1.Year] = {2018}>} Amount), Sum(Amount))

• ###### Re: GetSelectedCount with sumif

Sunny i replied that on my own. but as it many times I see yours reply is there

You are missing 2018 and I am missing last )

• ###### Re: GetSelectedCount with sumif

Hahahaha yes, you are right

• ###### Re: GetSelectedCount with sumif

Thank you both!!

• ###### Re: GetSelectedCount with sumif

Is it possible to do this with a nested aggregation somehow? I would like to use the same logic for a calculation within the GetSelectedCount:

Count([Emplid])/Count(EMPLID_PIT)

Thanks!

• ###### Re: GetSelectedCount with sumif

can you explain a little bit more what are you looking for

• ###### Re: GetSelectedCount with sumif

I would like to use the same expression as I did with the Sum(Amount):

If(GetSelectedCount([Sheet1.Year]) = 0, Sum({<[Sheet1.Year] = {2018}>} Amount), Sum(Amount))

This is what I have tried:

If(GetSelectedCount([Sheet1.Year]) = 0, Sum({<[Sheet1.Year] = {2018}>} [Emplid])/Count(EMPLID_PIT)), Count([Emplid])/Count(EMPLID_PIT))

• ###### Re: GetSelectedCount with sumif

If(GetSelectedCount([Sheet1.Year]) = 0, (Sum({<[Sheet1.Year] = {2018}>} [Emplid]))/Count(EMPLID_PIT), Count([Emplid])/Count(EMPLID_PIT))

i dont know if you are looking for sum of Emplid or may be count

If(GetSelectedCount([Sheet1.Year]) = 0, (count({<[Sheet1.Year] = {2018}>} [Emplid]))/Count(EMPLID_PIT), Count([Emplid])/Count(EMPLID_PIT))

• ###### Re: GetSelectedCount with sumif

Try this

If(GetSelectedCount([Sheet1.Year]) = 0, Count({<[Sheet1.Year] = {2018}>}[Emplid])/Count({<[Sheet1.Year] = {2018}>}EMPLID_PIT), Count([Emplid])/Count(EMPLID_PIT))

• ###### Re: GetSelectedCount with sumif

That works.  I really appreciate you guys' help!