Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
you can replace your IF's with Set analysis for better performance.
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
)
)
create variables for
vInvoiceDateDT = InvoiceDateDT
vCallDateDT = CallDateDT
vDateDiff = $(InvoiceDateDT) -$(CallDateDT)
Hi Nagaraj,
Thanks for reply. when I add these new expressions the results are coming wrong compared to old data
Thanks,
Siva
Hi Siva,
Both the expressions are showing incorrect results? Could you attach a sample file and expected output?
Hi Nagaraj,
PFA, yes both expression giving incorrect result
thanks,
Siva
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
)
)
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
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