Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Doubt regarding a formula withSET ANALYSIS OR AGGR

Hi

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

1 Solution

Accepted Solutions
loveisfail
Not applicable

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Remove "$" as Jayant Suggested

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

Life is so rich, and we need to respect to the life !!!
18 Replies
loveisfail
Not applicable

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

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

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

Life is so rich, and we need to respect to the life !!!
Not applicable

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

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

jayanttibhe
Not applicable

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Maybe like this :

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

jayanttibhe
Not applicable

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

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

loveisfail
Not applicable

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Remove "$" as Jayant Suggested

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

Life is so rich, and we need to respect to the life !!!
loveisfail
Not applicable

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

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

Life is so rich, and we need to respect to the life !!!
jayanttibhe
Not applicable

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

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

Not applicable

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

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

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

thanks for your help

regards!!!

PMJT

loveisfail
Not applicable

Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

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

Life is so rich, and we need to respect to the life !!!