topic Re: Sum column using an aggr if function in App Development

Sum column using an aggr if function

Anonymous — Thu, 22 Dec 2016 09:04:15 GMT

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.

Re: Sum column using an aggr if function

Anonymous — Thu, 22 Dec 2016 09:49:28 GMT

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)

Re: Sum column using an aggr if function

sunny_talwar — Thu, 22 Dec 2016 10:35:59 GMT

May be you just need this:

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

Re: Sum column using an aggr if function

Anonymous — Thu, 22 Dec 2016 10:41:39 GMT

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.

Re: Sum column using an aggr if function

sunny_talwar — Thu, 22 Dec 2016 10:45:45 GMT

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

Re: Sum column using an aggr if function

Anonymous — Thu, 22 Dec 2016 10:53:36 GMT

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?

Re: Sum column using an aggr if function

sunny_talwar — Thu, 22 Dec 2016 11:03:49 GMT

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)

Re: Sum column using an aggr if function

Anonymous — Thu, 22 Dec 2016 11:08:58 GMT

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.

Re: Sum column using an aggr if function

sunny_talwar — Thu, 22 Dec 2016 11:48:36 GMT

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?

Re: Sum column using an aggr if function

Anonymous — Thu, 22 Dec 2016 12:02:52 GMT

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.

Re: Sum column using an aggr if function

sunny_talwar — Thu, 22 Dec 2016 12:06:25 GMT

QlikView is case sensitive, do you know if the field is called Created xad or Created XAD or some variation of it?

Re: Sum column using an aggr if function

Anonymous — Thu, 22 Dec 2016 12:16:48 GMT

I'm using Qlik Sense, so not sure if that makes a difference. The field is Created xad

What is interesting is that the count expression works perfectly, I created another filter called "User Type", if I add this in the filter panel and select external the count does not change at all, but the sum then drops to the correct level.

So the issue would appear to be that it is ignoring the Created xad filter for some reason.

Re: Sum column using an aggr if function

Anonymous — Thu, 22 Dec 2016 12:18:27 GMT

So the top image shows the correct GWP, note how the count stays the same with your expression:

Re: Sum column using an aggr if function

sunny_talwar — Thu, 22 Dec 2016 12:26:16 GMT

What is the difference between Created xad and User Type field? If you made use of User Type to make selection to get the desired result, do you think you might need User Type in your set analysis instead of Created xad?

Re: Sum column using an aggr if function

sunny_talwar — Thu, 22 Dec 2016 12:27:00 GMT

How about this?

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

Re: Sum column using an aggr if function

Anonymous — Thu, 22 Dec 2016 12:29:29 GMT

You finally found a combination that Qlik likes! Thanks Sunny, I'd never have gotten to the bottom of it by myself.