topic Re: A Problem on Data Grouping in QlikView

A Problem on Data Grouping

LynnLI — Wed, 19 Jul 2023 11:01:12 GMT

Hi Buddy,

Each week we sold thousands of articles and would like to group these articles according to three groups:
A STOCKS are top articles accounted for 60% of total sales, B STOCKS accounted for the next 30% of total sales, and the left 10% are C STOCKS.

The problem is the dimension of grouping is only applicable for the total number😂.

If the requirement is to apply this logic to each week, how to remedy the dimension logic as follow:

=aggr(

if(rangesum(above(sum({<Year={"$(=$(vmaxyear))"},Week={"<=$(=Week(today()-7))"}>} sales)/sum(total {<Year={"$(=$(vmaxyear))"},Week={"<=$(=Week(today()-7))"}>} sales),1,RowNo()))<0.6,'A STOCKS',

if(rangesum(above(sum({<Year={"$(=$(vmaxyear))"},Week={"<=$(=Week(today()-7))"}>} sales)/sum(total {<Year={"$(=$(vmaxyear))"},Week={"<=$(=Week(today()-7))"}>} sales),1,RowNo()))<0.9,'B STOCKS',

'C STOCKS')),(ArticleCode,(=SUM({<Year={"$(=$(vmaxyear))"},Week={"<=$(=Week(today()-7))"}>} sales),Desc)))

Attached is the layout result for reference, which is not correct😅.

Looking forward to your answer.

Best Regard!

Lynn Li

Re: A Problem on Data Grouping

Phoebe5776 — Thu, 27 Jul 2023 12:03:36 GMT

To apply the A/B/C stock logic on a per week basis, we need to calculate the category thresholds dynamically based on the sales for each week. Here is one way to do it in DAX:

Stock Category = VAR TotalSales = SUMX(SUMMARIZE('Sales', 'Sales'[ArticleCode], 'Sales'[Week], "Sales Total", SUM('Sales'[Sales])), [Sales Total]) VAR CumulativeSales = CALCULATE( SUM('Sales'[Sales]), FILTER(ALL('Sales'), 'Sales'[Week] <= MAX('Sales'[Week])), GROUPBY('Sales'[ArticleCode], 'Sales'[Week]) ) RETURN SWITCH( TRUE(), CumulativeSales <= 0.6 * TotalSales, "A Stocks", CumulativeSales <= 0.9 * TotalSales, "B Stocks", "C Stocks" )

See here: myenvoyair login

This calculates the total sales for each week, then uses a cumulative sum to calculate the running percentage of sales for each article up to that week. We can then categorize based on whether the cumulative percentage is less than 60% or 90% of the total for that week.

Re: A Problem on Data Grouping

LynnLI — Thu, 27 Jul 2023 02:50:47 GMT

Finally,

We combined articlecode & Week together to aggregate and came to the correct result.

Here is the dimension in case any user in need:

=aggr(IF(SUM({<Year={"$(=$(vmaxyear))"}>}sales)>0,

if(rangesum(above(sum({<Year={"$(=$(vmaxyear))"}>}sales)/sum(total<Week> {<Year={"$(=$(vmaxyear))"}>}sales),1,RowNo()))<0.6,'A STOCKS',

if(rangesum(above(sum({<Year={"$(=$(vmaxyear))"}>}sales)/sum(total<Week> {<Year={"$(=$(vmaxyear))"}>}sales),1,RowNo()))<0.9,'B STOCKS',

'C STOCKS')),),Week,(ArticleCode_WeekName,(=SUM({<Year={"$(=$(vmaxyear))"}>}sales),Desc)))