Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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 (

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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 (

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)