How to tune below expressions?

[siva_boggarapu]

Hi,

I have table with the below expressions. The below expressions are giving me bad performance while I am doing in selection

Is there any better way we can modify the below table expressions?

Unique Leads per Center:

avg(aggr(count(distinct CallerNumber), ShopNumber))

Lead Conversion %:

count(distinct IF(InvoiceDateDT - CallDateDT >= 0 and InvoiceDateDT - CallDateDT <= $(vConversionLimit), CallerNumber)) / count(distinct CallerNumber)

Conversion ARO:

avg(

aggr(

    max(

        IF(

            InvoiceDateDT - CallDateDT >= 0 and InvoiceDateDT - CallDateDT <= $(vConversionLimit),

                NetAmount

            )

        )

    ,InvoiceHeaderId

    )

)

Thanks,

Siva

[YoussefBelloum]

Hi,

you can replace your IF's with Set analysis for better performance.

[tamilarasu]

Hello,

Unique Leads per Center:

avg(aggr(count(distinct CallerNumber), ShopNumber))

Lead Conversion %:

count(distinct {<CallerNumber={"=InvoiceDateDT - CallDateDT >= 0 and InvoiceDateDT - CallDateDT <= $(vConversionLimit)"}>}CallerNumber) / count(distinct CallerNumber)

Conversion ARO:

avg(

  aggr(

          max( {<InvoiceHeaderId ={"=InvoiceDateDT - CallDateDT >= 0 and InvoiceDateDT - CallDateDT <=                             $(vConversionLimit)"}>} NetAmount

                )

      ,InvoiceHeaderId

         )

      )

[ChennaiahNallani]

create variables for

vInvoiceDateDT = InvoiceDateDT

vCallDateDT = CallDateDT

vDateDiff = $(InvoiceDateDT) -$(CallDateDT)

[siva_boggarapu]

Hi Nagaraj,

Thanks for reply. when I add these new expressions the results are coming wrong compared to old data

Thanks,

Siva

[tamilarasu]

Hi Siva,

Both the expressions are showing incorrect results? Could you attach a sample file and expected output?

[siva_boggarapu]

Hi Nagaraj,

PFA, yes both expression giving incorrect result

thanks,

Siva

[tamilarasu]

Hi Siva,

Lead Conversion %:

count(distinct {<Territory={"=InvoiceDateDT - CallDateDT >= 0 and InvoiceDateDT - CallDateDT <= $(vConversionLimit)"}>}CallerNumber) / count(distinct CallerNumber)

Conversion ARO:

avg(

  aggr(

          max( {<Territory={"=InvoiceDateDT - CallDateDT >= 0 and InvoiceDateDT - CallDateDT <=                             $(vConversionLimit)"}>} NetAmount

                )

      ,InvoiceHeaderId

         )

      )

[siva_boggarapu]

Hi Nagaraj,

Sorry to say you I am not getting correct result set anymore. Can I add above expression in Script. If yes, let me know how to add those expressions in Script level?

Thanks,

siva

[tamilarasu]

Hi Siva,

I would like to check your file. Please attach it or at least sample file.

Preparing examples for Upload - Reduction and Data Scrambling