Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution.
**READ ALL ABOUT IT!**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- A Problem on Data Grouping

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

LynnLI

Contributor II

2023-07-19
07:01 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

A Problem on Data Grouping

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

1 Solution

Accepted Solutions

Phoebe5776

Contributor

2023-07-26
04:26 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

2 Replies

Phoebe5776

Contributor

2023-07-26
04:26 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

LynnLI

Contributor II

2023-07-26
10:50 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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))) |