- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How about this?
Sum({<[Broker Name] = {"=Count({$<Quote_Submitted_Count ={1}, [Created xad] ={1}>} TransactionID) > 9"}, Quote_Secured_Count={"1"}, [Created xad] ={1}>} GWP)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be you just need this:
Sum(Aggr(If(Count({$<Quote_Submitted_Count ={1}, [Created xad] ={"1"}>} TransactionID) > 9, GWP), [Broker Name]))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- « Previous Replies
-
- 1
- 2
- Next Replies »