Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Anil_Babu_Samineni

Remove "$" as Jayant Suggested

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

18 Replies
Anil_Babu_Samineni

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

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Not applicable
Author

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
Creator III
Creator III

Maybe like this :

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

jayanttibhe
Creator III
Creator III

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

Anil_Babu_Samineni

Remove "$" as Jayant Suggested

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
jayanttibhe
Creator III
Creator III

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
Author

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

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

thanks for your help

regards!!!

PMJT

Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful