Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

saarek01
New Contributor III

Sum column using an aggr if function

I have a working aggr if expression kindly created by SunnyT on here last week, I need this formula to now have an additional steps and cannot seem to get it to work: Sum(Aggr(If(Count({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID) > 9, 1, 0), [Broker Name]))

The above expression identifies the number of quotes created by user and if the total number exceeds 10 then their submissions are counted within a simple KPI.

What I now need is the following:

Show the amount of sales in financial terms for those users. So the two new markers would be Quote_Secured_Count and then it would need to sum the money which is held under GWP.

The logic must only sum the GWP where Quote_Secured_Count = 1 for the users where the Quote_Submitted_Count exceeded 10.

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Sum column using an aggr if function

How about this?

Sum({<[Broker Name] = {"=Count({$<Quote_Submitted_Count ={1}, [Created xad] ={1}>} TransactionID) > 9"}, Quote_Secured_Count={"1"}, [Created xad] ={1}>} GWP)

15 Replies
saarek01
New Contributor III

Re: Sum column using an aggr if function

Have tried using a completely different expression, but no success:

IF(Count({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID)>9, Sum({$<Quote_Secured_Count={"1"}>} GWP),0)

MVP
MVP

Re: Sum column using an aggr if function

May be you just need this:

Sum(Aggr(If(Count({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID) > 9, GWP), [Broker Name]))

saarek01
New Contributor III

Re: Sum column using an aggr if function

Hello again Sunny!

Unfortunately the formula results in £0 result, it's also important that the Quote_Secured_Count marker = 1 because there are values for all entries in the GWP column and so we only want to pull through those with the 1.

MVP
MVP

Re: Sum column using an aggr if function

I guess if without any filter you are getting 0, adding another set analysis filter the number is going to go down unless there are huge negatives when Quote_Secured_Count <> 1.

I guess, give this a shot?

Sum(Aggr(If(Count({$<Quote_Submitted_Count ={1}, [Created xad] = {"1"}, Quote_Secured_Count={"1"}>} TransactionID) > 9, GWP), [Broker Name]))

saarek01
New Contributor III

Re: Sum column using an aggr if function

Still coming back as £0. Is it because the GWP column would have to be a sum, seeing as it houses financial amounts and the formula is currently based on count logic?

MVP
MVP

Re: Sum column using an aggr if function

Try this new approach:

For Count

Count(DISTINCT {<[Broker Name] = {"=Count({$<Quote_Submitted_Count ={1}, [Created xad] ={1}>} TransactionID) > 9"}>} [Broker Name])

For Sum

Sum({<[Broker Name] = {"=Count({$<Quote_Submitted_Count ={1}, [Created xad] ={1}>} TransactionID) > 9"}, Quote_Secured_Count={"1"}>} GWP)

saarek01
New Contributor III

Re: Sum column using an aggr if function

Interesting, I am now getting a GWP value. But it is for all GWP where Quote Secured Count = 1 as opposed to only showing the value for the brokers that had 10 or more active quotes.

The count expression works exactly as you planned.

MVP
MVP

Re: Sum column using an aggr if function

Can you create a table where you have Broker Name as dimension and these two expressions

1) Sum({<[Broker Name] = {"=Count({$<Quote_Submitted_Count ={1}, [Created xad] ={1}>} TransactionID) > 9"}, Quote_Secured_Count={"1"}>} GWP)

2) =Count({$<Quote_Submitted_Count ={1}, [Created xad] ={1}>} TransactionID)

and post a screenshot of this chart?

saarek01
New Contributor III

Re: Sum column using an aggr if function

Unfortunately due to data protection I cannot post a picture of the actual information. I can confirm that I've done what you requested, what is interesting is that the sum formula appears to be ignoring the Created xad filter. So the reason it is counting all amounts is that without the xad filter of 1 being in play the sum would be above 10 for most users.