Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

christianeatman
New Contributor

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!

Tags (2)
1 Solution

Accepted Solutions
zebhashmi
Valued Contributor

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 )

11 Replies
zebhashmi
Valued Contributor

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)

MVP
MVP

Re: GetSelectedCount with sumif

Try this

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

zebhashmi
Valued Contributor

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 )

MVP
MVP

Re: GetSelectedCount with sumif

Hahahaha yes, you are right

christianeatman
New Contributor

Re: GetSelectedCount with sumif

Thank you both!!

christianeatman
New Contributor

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!

zebhashmi
Valued Contributor

Re: GetSelectedCount with sumif

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

christianeatman
New Contributor

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))


But instead of Sum(Amount) I want Count([Emplid])/Count(EMPLID_PIT). I receive an error about nested aggregations.


This is what I have tried:


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

zebhashmi
Valued Contributor

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))