Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

siva_boggarapu
Contributor II

How to tune below expressions?

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

11 Replies
YoussefBelloum
Esteemed Contributor

Re: How to tune below expressions?

Hi,

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

Re: How to tune below expressions?

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

Re: How to tune below expressions?

create variables for

vInvoiceDateDT = InvoiceDateDT

vCallDateDT = CallDateDT

vDateDiff = $(InvoiceDateDT) -$(CallDateDT)

siva_boggarapu
Contributor II

Re: How to tune below expressions?

Hi Nagaraj,

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

Thanks,

Siva

Re: How to tune below expressions?

Hi Siva,

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

siva_boggarapu
Contributor II

Re: How to tune below expressions?

Hi Nagaraj,

PFA, yes both expression giving incorrect result

thanks,

Siva

Re: How to tune below expressions?

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
Contributor II

Re: How to tune below expressions?

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

Re: How to tune below expressions?

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

Community Browser