topic Re: Pareto Distinct Count in QlikView

Pareto Distinct Count

Anonymous — Tue, 27 May 2014 09:34:02 GMT

Hi all!

I need to solve this tricky problem and I really appreciate to receive your help.

In my DataSource I have a fact table with some sales records releated to a lookup table with some customers details.

I built a report for PARETO ANALYSIS with one table and one slider (for percentage set-up).

I'm trying to add a text object, not related with first pareto-table, to count the number of customers under the selected pareto percentage

(A-Class Customers).

Some other informations:

- the sales value is releated to current year

- the name of the "sales-field" is in a variable

I'm triyng to change this formula because i think it doesn't count the customers name distinctly (proper count=155, text obj count=897)

TEST OBJECT EXPRESSION

=Count( DISTINCT If((Aggr(RangeSum(Top(Sum({<Year={$(varCurrentYear)},Month={"<=$(varCurrentMonth)"},Day={"<=31"}>}$(varSales)), 1, RowNo()))

/ RangeSum(Top(Sum({<Year={$(varCurrentYear)},Month={"<=$(varCurrentMonth)"},Day={"<=31"}>}$(varSales)), 1, NoOfRows())), Customer_ds)*100)

< $(varParetoPerc), Customer_ds, Null()))

Re: Pareto Distinct Count

jvitantonio — Tue, 27 May 2014 09:46:55 GMT

Hi Manuel,

Do you think you can add your sample application? It's going to be easier to spot the problem.

Thanks

Re: Re: Pareto Distinct Count

Anonymous — Tue, 27 May 2014 10:40:55 GMT

Hi JV, here's the example

Re: Re: Re: Pareto Distinct Count

jvitantonio — Tue, 27 May 2014 10:59:15 GMT

Ciao Manuel,

Here's the app modified.

Basically, you are using the wrong sum(FATURATTO), because in your chart, you are using the following:

sum({<Year={$(varCurrentYear)},Month={"<=$(varCurrentMonth)"},Day={"<=31"}>}FATTURATO)

but in your text box, you used only Sum(FATURATTO).

I've added 3 columns at the end of your table, so you see the result with this modified. Once you remove these columns, you won't see Company9, Company11 and Company12 which have FATTURATO = 0.

A presto

Re: Pareto Distinct Count

Anonymous — Tue, 27 May 2014 16:41:15 GMT

It works well, Thank You!

Re: Pareto Distinct Count

jvitantonio — Tue, 27 May 2014 16:47:15 GMT

You are welcome. Please mark the answer as correct.

Thanks,

Re: Pareto Distinct Count

Anonymous — Wed, 28 May 2014 08:16:25 GMT

The expression works well, theoretically, but in my big data source it doesn't.

The follow expression is the one I want to implement in my report, in a txt object, , wich is structurally identical to the previous one, but it doesn't count correctly...

=if(varParetoArticoli=0,

Count(DISTINCT If(

(Aggr(RangeSum(Top(Sum({<Year={$(varCurrentYear)},Month={"<=$(varCurrentMonth)"},Day={"<=31"}>}$(varFatturato)), 1, RowNo())),CLI_DES)

/ Aggr(RangeSum(Top(Sum({<Year={$(varCurrentYear)},Month={"<=$(varCurrentMonth)"},Day={"<=31"}>}$(varFatturato)), 1, NoOfRows())), CLI_DES)

)

<= ($(varParetoPerc)/100), CLI_DES))

Count(DISTINCT If(

(Aggr(RangeSum(Top(Sum({<Year={$(varCurrentYear)},Month={"<=$(varCurrentMonth)"},Day={"<=31"}>}$(varFatturato)), 1, RowNo())),ART_COD)

/ Aggr(RangeSum(Top(Sum({<Year={$(varCurrentYear)},Month={"<=$(varCurrentMonth)"},Day={"<=31"}>}$(varFatturato)), 1, NoOfRows())), ART_COD)

)

<= ($(varParetoPerc)/100), ART_COD))

)

Re: Pareto Distinct Count

jvitantonio — Wed, 28 May 2014 09:32:35 GMT

Manuel,

Can you please share again your complete app? You can scramble the data.

Thanks,

Re: Re: Pareto Distinct Count

Anonymous — Wed, 28 May 2014 10:00:54 GMT

I have attached the screen with the report and the expression

Re: Re: Pareto Distinct Count

Anonymous — Wed, 28 May 2014 10:15:52 GMT

Basically, the expression for the table object doesn't use aggr function to specify the fields and the expression for text object does.

In the "tab expression" I place the countDISTINCT function entirely after the if condition, and in the text object DISTINCT command is placed before the percentage calculation. I think the system doesn't recognize that command.