topic Re: Doubt regarding a formula withSET ANALYSIS OR AGGR in QlikView

Doubt regarding a formula withSET ANALYSIS OR AGGR

Fri, 10 Feb 2017 01:27:31 GMT

I don't know if my problem have to be solved with what option: i have to count the number of invoices that in the total have 3 units or less units invoiced.

I had tried with these 2 options but for some reason they don't work. On the other hand I don't know which is the best option of them to solve this problem, could you explain why???

the formulas that I wrote are these:

formula 1: Count(AGGR(SUM([UN VENDIDAS])<=3, NUM.FACTURA)) - this formula is not working well because its counting the invoices that have lines with 3 or less units instead of just counting the invoices that have 3 or less units in the total.

formula 2: Count({< Sum ([UN VENDIDAS])<=3 >} DISTINCT NUM.FACTURA) - this formula is showing error in the syntaxis

Thanks for your help

Regards,

PMJT

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Anil_Babu_Samineni — Fri, 10 Feb 2017 01:40:03 GMT

Leave it the First one, Try a luck for Second one

Count({< [NUM.FACTURA] = {'$(=Sum ([UN VENDIDAS])<=3)' >} DISTINCT [NUM.FACTURA])

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Fri, 10 Feb 2017 02:05:25 GMT

Hi Anil

I had tried with your proposal but isn't working, do you have another idea,

I also tried with this one because i thinked that yours have "}" missed but also fails

Count({< [NUM.FACTURA] = {'$(=Sum ([UN VENDIDAS])<=3)'} >} DISTINCT [NUM.FACTURA])

thanks for your comments,

PMJT

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

jayanttibhe — Fri, 10 Feb 2017 02:06:00 GMT

Maybe like this :

Count({< [NUM.FACTURA] = {"=Sum(UN VENDIDAS]) <=3"} >} DISTINCT [NUM.FACTURA]))

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

jayanttibhe — Fri, 10 Feb 2017 02:08:20 GMT

Hi Anil, Sorry but are you trying to Search inside Set condition ? I dont think you need to use $ expansion within. Also, Single Quotes should not be use for Set Search conditions. Though it works in some of the cases - Its known Set analysis issue.

Please correct if I am wrong.

Thx

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Anil_Babu_Samineni — Fri, 10 Feb 2017 02:11:03 GMT

Remove "$" as Jayant Suggested

Count({< [NUM.FACTURA] = {'$(=Sum ([UN VENDIDAS])<=3)'} >} DISTINCT [NUM.FACTURA]) //Strike through the $ and (

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Anil_Babu_Samineni — Fri, 10 Feb 2017 02:14:37 GMT

True, Off course that my first post is from Mobile. I had checked with that. It worked

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

jayanttibhe — Fri, 10 Feb 2017 02:15:32 GMT

Also,

If numeric values, then no need for quotes
If literals / fixed strings, then single quotes
If search patterns, then double quotes

Re: Sets when NOT equal to

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Fri, 10 Feb 2017 02:25:14 GMT

Perfect!!!!!! the final formula is:

Count({< [NUM.FACTURA] = {'=Sum([UN VENDIDAS]) <=3'} >} DISTINCT [NUM.FACTURA])

thanks for your help

regards!!!

PMJT

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Anil_Babu_Samineni — Fri, 10 Feb 2017 02:28:36 GMT

Great, then close this Thread

Qlik Community Tip: Marking Replies as Correct or Helpful

Note: If some one has same issue then they will land up the same thread. Then only they came to know follow up the Helpful answer and Correct Answers.

Best

ANIL

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Fri, 10 Feb 2017 03:00:41 GMT

Hi once again

im trying with this formula to discard 0 records but its not working:

Count({< [NUM.FACTURA] = {'=Sum([UN VENDIDAS])= {1,2,3}'} >} DISTINCT [NUM.FACTURA])

what should i change???

thanks for your comments

regards,

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Anil_Babu_Samineni — Fri, 10 Feb 2017 05:25:54 GMT

That won't work because How Sum([UN VENDIDAS]) has the values are 1,2,3

You mean to say this?

Count({< [NUM.FACTURA] = {'=Sum([UN VENDIDAS])= {1 or 2 or 3}'} >} DISTINCT [NUM.FACTURA])

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Wed, 15 Feb 2017 17:26:37 GMT

Hi Anil

Sorry for not answering before,.,.,I had tried your solution however it dont work. On the other hand i have a question: if i would want consider a bigger range I had saw many options with this kind of script

Count({< [NUM.FACTURA] = {'=Sum([UN VENDIDAS])= {">0","<=3"}'} >} DISTINCT [NUM.FACTURA])

do you know why its not working?

thanks for your help

regards,

PMJT

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

sunny_talwar — Wed, 15 Feb 2017 17:29:29 GMT

You can do this:

Count({<[NUM.FACTURA] = {"=Sum([UN VENDIDAS]) > 0 and Sum([UN VENDIDAS]) <= 3"} >} DISTINCT [NUM.FACTURA])

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Anil_Babu_Samineni — Wed, 15 Feb 2017 17:30:43 GMT

Ranges should be syntax as follow

Count({< [NUM.FACTURA] = {'=Sum([UN VENDIDAS])= {">0 <=3"}'} >} DISTINCT [NUM.FACTURA])

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Wed, 15 Feb 2017 17:45:33 GMT

Thanks a lot Sunny, your solution worked perfectly. now i see that a "problem" in Qlikview could have many approaches however im still dont understand why one solution could work with some context and in other context dont.

Regards,

PMJT

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Wed, 15 Feb 2017 17:47:40 GMT

Hi Anil

I tried with your changes however it didn't work. Do you have any idea why?

regards,

PMJT

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

sunny_talwar — Wed, 15 Feb 2017 17:47:50 GMT

I think a good place to start for set analysis would be this

A Primer on Set Analysis

and also read this:

Set Analysis: syntaxes, examples

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

sunny_talwar — Wed, 15 Feb 2017 17:49:03 GMT

Syntax isn't right pedro... I am not sure what the idea was behind Anil's expression, but it isn't the right way to code it