Sum column using an aggr if function

[Anonymous]

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.

[Anonymous]

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)

[sunny_talwar]

May be you just need this:

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

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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?

[sunny_talwar]

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)

[Anonymous]

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.

[sunny_talwar]

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?

[Anonymous]

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.