8 Replies Latest reply: May 19, 2016 6:29 AM by Stefan Wühl

# Set Analysis - using min function with a condition

Hi Experts,

I am trying to count the numbers of customers who got converted in the same year they first bought the product. Here the conversion is customers who bought certain amount of units, this value is provided by the user and stored in a variable "vUnits".

The Dimension is FirstOrderYear and the expression is

count({<FirstOrderYear={'=\$(=year(Min({<qty={'>=20'}>}date_invoice)))'}>}partner_id)

Basically to put it simply, count of all the customers where the FirstConversionYear is equal to FirstOrderYear where FirstConversionYear is Min({<qty={'>=\$(vUnits)'}>}date_invoice)

This expression does not seem to give me the correct result set. I have tried with other combinations like Aggr, Firstsortedvalue, etc but none of them work.

Any suggestions would help. Thanks

Thanks

Swetha

• ###### Re: Set Analysis - using min function with a condition

I believe the issue arise from the fact that

{<FirstOrderYear={'=\$(=year(Min({<qty={'>=20'}>}date_invoice)))'}>}

is not looking at partner specific data, neither FirstOrderYear, nor date_invoice filtered by Min qty,while I assume you expect exactely this.

• ###### Re: Set Analysis - using min function with a condition

What is the relation between FirstConversionYear and FirstOrderYear here? Can you made provide some sample data with the expected output to check this out?

• ###### Re: Set Analysis - using min function with a condition

gwassenaar and @Sunny T,

I have attached a sample QVF . The expected outcome in the Same year conversion column should be

2009 - 32

2010 - 13

Any suggestions would help

Thanks

Swetha

• ###### Re: Set Analysis - using min function with a condition

Try creating a field with that year in your script, maybe something like

partner_id,

min(dateinyears) as FirstOrderYear,

max(dateinyears) as LastOrderYear,

Min( If(qty>20, dateinyears)) as FirstWhateverYear

RESIDENT account_invoice_line

group by partner_id;

Then use this field in your comparison, like

count(DISTINCT  If(FirstOrderYear=FirstWhateverYear, partner_id))

• ###### Re: Set Analysis - using min function with a condition

That was my first approach but then the users want to define the value of No of units which here is hard-coded to 20. They want it to be flexible so that they define the conversion figure. That's the reason I am using a extension to get the user input into a variable "vUnits". But this variable which includes the user input can only be used at the chart level. I cant use it in the script for the calculation.

Any other suggestion would be helpful.

Thanks

Swetha

• ###### Re: Set Analysis - using min function with a condition

BTW, do you really want to compare your threshold to a single qty line item value, or do you want to aggregate the qty to year level, then compare to your threshold?

• ###### Re: Set Analysis - using min function with a condition

@Swuehl,

We have to aggregate the quantity and then compare.

Thanks

Swetha

• ###### Re: Set Analysis - using min function with a condition

Maybe like this?