topic Re: Set analysis-How to only show results greater than a certain value in QlikView

Set analysis-How to only show results greater than a certain value

paul_ripley — Wed, 27 Jun 2018 12:49:29 GMT

Hello

The set analysis below produces the table below.

I would only really like to show any portfolio where the amount is greater than 500,000 ie rounded to the nearest 1million,

I would like to do this in set analysis as I would need to reproduce in N Printing as a formula

sum({< proceeds = {"Proceeds"}>} [GPR GBP])*-1

Would anyone be able to help with this?

Many thanks

Paul

Re: Set analysis-How to only show results greater than a certain value

Chanty4u — Wed, 27 Jun 2018 12:53:19 GMT

may be this

sum({< proceeds = {">$(vLimit"}>} [GPR GBP])*-1

letting vLimit = 500000

Re: Set analysis-How to only show results greater than a certain value

sunny_talwar — Wed, 27 Jun 2018 12:56:23 GMT

500,000 is less than 1million... So, if we are rounding... anything more than 500,000 will be rounded to 1 million... so, essentially, anything above 500k and below 1mil... will show up as 1 mill?

May be this

If(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1 > 500000, Round(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1, 1E6))

Re: Set analysis-How to only show results greater than a certain value

paul_ripley — Wed, 27 Jun 2018 16:21:13 GMT

Hi Sunny

It kind of worked. My table now shows one line of 4m

But when I put it into a text box, which is ultimately what I need to do it gives me 5m?

Re: Set analysis-How to only show results greater than a certain value

paul_ripley — Wed, 27 Jun 2018 16:22:17 GMT

HI Chanty

Thanks for replying but that didn't work unfortunately

Re: Set analysis-How to only show results greater than a certain value

sunny_talwar — Wed, 27 Jun 2018 16:23:06 GMT

Try this

Sum(Aggr(

If(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1 > 500000, Round(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1, 1E6))

, business_reclass, portfolio, portfolio_text))

Re: Set analysis-How to only show results greater than a certain value

paul_ripley — Wed, 27 Jun 2018 16:40:50 GMT

Hi Sunny

That gave a value of zero

Paul

Re: Set analysis-How to only show results greater than a certain value

sunny_talwar — Wed, 27 Jun 2018 16:42:30 GMT

May be the set analysis is missing from outer aggregation

Sum({<proceeds = {'Proceeds'}>}Aggr(

If(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1 > 500000, Round(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1, 1E6))

, business_reclass, portfolio, portfolio_text))

Re: Set analysis-How to only show results greater than a certain value

sunny_talwar — Wed, 27 Jun 2018 16:43:50 GMT

Also, I might have misstyped the name of your first dimension

Sum({<proceeds = {'Proceeds'}>}Aggr(

If(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1 > 500000, Round(Sum({<proceeds = {'Proceeds'}>} [GPR GBP]) * -1, 1E6))

, bussline_reclass, portfolio, portfolio_text))

Re: Set analysis-How to only show results greater than a certain value

paul_ripley — Wed, 27 Jun 2018 16:45:24 GMT

Sorry, that still gives zero

Re: Set analysis-How to only show results greater than a certain value

paul_ripley — Wed, 27 Jun 2018 16:46:00 GMT

oh let me check,might be a spelling mistake

Re: Set analysis-How to only show results greater than a certain value

paul_ripley — Wed, 27 Jun 2018 16:48:13 GMT

Yes thanks for your help Sunny (I just had to change business to bussline.

Thanks you've helped me a lot and I am very grateful!

Re: Set analysis-How to only show results greater than a certain value

paul_ripley — Wed, 27 Jun 2018 16:57:54 GMT

Sorry another question, would you know why when I put this formula into N Printing it comes out as 1327000000, but it works in QlikView?

Re: Set analysis-How to only show results greater than a certain value

sunny_talwar — Wed, 27 Jun 2018 17:03:47 GMT

Do you have selections in QlikView dashboard?

Re: Set analysis-How to only show results greater than a certain value

paul_ripley — Wed, 27 Jun 2018 17:05:52 GMT

Yes I think that's it. Many thanks. You're a star!