Pareto Analysis per month

[Marcusdoering]

Hello together,
i try to create an Pareto analysis per month and I will show the solution in a bar chart.

I try to show, how many customers are buying X% of the goods on a monthly basis.

I have the following problem. If I pick just one month in my bar chart the result is correct. If I delete the month filter (show the whole timeline) the result is not correct any more.

The code I use is the following

=Sum(
Aggr(
If(
RangeSum(
Above(
Count(INCOICE_NO)
/
Count(total <YEARMONTH> INCOICE_NO)
,0,RowNo()
)
)<$(vParetoThreshold),1,0
)
,
YEARMONTH,
(CUST_ID,(=Count(INCOICE_NO),DESC))),
)

Its looks like that the RangeSum() Above() is not working correctly. Or the sorting is not working correct if more then one month is selected.

Does anybody has an idea?

Thank you very much in advanced.

[marcus_sommer]

Before adjusting in any way the aggr() you could try if an extension of the above() and/or the rowno() with a TOTAL statement might be helpful.

Beside this I suggest to do the trial and error not in a bar-chart else in a table and using there all expression-parts as parallel measures to see how they behave and change if you apply various tests.

[Chanty4u]

Hi

Try this 

Sum(

    Aggr(

        If(

            RangeSum(

                Above(

                    Count(INVOICE_NO) /

                    Count(TOTAL <YEARMONTH> INVOICE_NO),

                    0,

                    RowNo()

                )

            ) <= $(vParetoThreshold),

            1,

            0

        ),

        YEARMONTH,

        (CUST_ID, (Count(INVOICE_NO), DESC))

    )

)

Or you can try this 

Sum(

    Aggr(

        If(

            RangeSum(

                Above(

                    Sum(Sales) / Sum(TOTAL <YEARMONTH> Sales),

                    0,

                    RowNo()

                )

            ) <= $(vParetoThreshold),

            1

        ),

        YEARMONTH,

        (CUST_ID, (Sum(Sales), DESC))

    )

)

[Marcusdoering]

Hi Chanty4u,

thank you for your support. I tested the code, but unfortunately it is not working as expected. I think I will try a ranking approach in the load script instead.