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

Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL:
**Learn More**

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Using Set Analysis to limit entries in a table

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

Not applicable

2016-05-09
10:07 AM

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

Using Set Analysis to limit entries in a table

I am working on a table that I have successfully limited to a subset of my data using various thresholds on various measure. Now, I need to calculate a measure that includes an overall value for just the entries in the table. Here is what I am dealing with.

My base table is populated something like this:

Product Category | Product Subcategory (Fltr) | YTD Sales | YTD GM % | Prev. YTD GM% | Eroded Gross Profit |
---|---|---|---|---|---|

Brass Widgets | Square | 1,000,000 | 10% | 11% | Need to calculate |

Brass Widgets | Round | 500,000 | 12% | 14% | Need to calculate |

Steel Widgets | Square | 2,500,000 | 7% | 8% | Need to calculate |

Steel Widgets | Round | 800,000 | 9% | 10% | Need to calculate |

To identify the product categories to include in the table, I use this expression for Product Category to identify the relevant product categories:

AGGR(

if($(SalesYTD)>400000,

if(

($(CYTD_GM) - $(LYTD_GM))< 0, [Product SubCategory])),

[Product Category], [Product SubCategory])

To this point, everything works fine. However, where I am struggling is how to calculate the Eroded Gross Profit. My equation for that is:

*Eroded Gross Profit = [YTD Sales]/(1-[Overall Prev. YTD GM %])*

The Overall Prev. YTD GM % is calculated for all the entries in the table above but *only* the entries in the table. In this case - let's assume that previous years sales are the same for simplicity - that value is 9.6%.

I tried to put in a set analysis expression in the Eroded Gross Profit column that would calculate the total Previous Year Sales and Previous Year GP in dollars in order to calculate the previous year GM%. However, that returned the total for the entire data set, not just the rows that are included in the above table.

Next, I tried the following:

Sum(TOTAL

{< [Product SubCategory] = {'$(=

AGGR(

if($(SalesYTD)>400000,

if(

($(CYTD_GM) - $(LYTD_GM))< 0, [Product SubCategory])),

[Product Category], [Product SubCategory])

)'

>}

[LYTD GP]

)

/

Sum(TOTAL

{< [Product SubCategory] = {'$(=

AGGR(

if($(SalesYTD)>400000,

if(

($(CYTD_GM) - $(LYTD_GM))< 0, [Product SubCategory])),

[Product Category], [Product SubCategory])

)'

>}

[LYTD Sales]

)

**Note:** [LYTD GP] is not in the table but exists in my app.

I want the above to calculate the total Gross Profit for all lines included in the table, divide that by the corresponding Total Sales for the items in the table from last year, in order to arrive at the overall weighted Gross Margin for the previous year. My thinking was to use the same expression that I used to create a "filtered" Product SubCategory dimension in a set analysis to return a list of relevant subcategories. However, when I put this into my expression editor, I wind up with all 0s.

Is what I am trying to achieve possible? Do I have a syntax problem? Is there another way to compute this? For now, I am mocking up a dashboard for beta testing. Once we go through that, our BI team can do more formal development and create additional aggregations/coding/other-magic-I-don't-understand in the background scripts when the data is loaded from the data warehouse. However, I need something that works for beta testing, even if it is not optimal from a development perspective.

Any advice is appreciated!

313 Views

0 Replies