Set Analysis - using min function with a condition

[#1]

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

[swuehl]

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.

But to help you more, you would need to describe your model much more detailed, best by uploading a sample QVF.

[sunny_talwar]

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

[#4]

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

[swuehl]

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

LOAD

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

[#6]

Thanks swuehl‌ for your response.

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

[swuehl]

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?

[#8]

@Swuehl,

We have to aggregate the quantity and then compare.

Thanks

Swetha

[swuehl]

Maybe like this?