# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Regards,

PMJT

1 Solution

Accepted Solutions
Highlighted

## 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 (

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)
18 Replies
Highlighted

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

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

PMJT

Highlighted
Contributor III

## Re: Doubt regarding a formula withSET ANALYSIS OR AGGR

Maybe like this :

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

Highlighted
Contributor III

## 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

Highlighted

## 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 (

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

## 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

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)
Highlighted
Contributor III

## 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

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

regards!!!

PMJT

Highlighted