Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

kathrynkvas
New Contributor II

KPI Drill Down from Overall to Groups to Determine % of Revenue for Products

I am having difficulty calculating a KPI. Below is the scenario I am attempting to capture in my KPI.


I have 50 total products. I dill down and and select 20 products (i.e. product group). I then drill down a step further to five products. I then want to get the % of revenue for those 5 products out of the 20 products I selected.

This is the equation I have tried. The current equation only gives the % of revenue for the 5 products out of the total 50 products revenue.  Note, I have to select October based on other visuals.

sum(revenue)/(sum({1<wMonth={October}>}total revenue))

10 Replies
arvind654
Honored Contributor II

Re: KPI Drill Down from Overall to Groups to Determine % of Revenue for Products

Test those 2 expression individually for the results?

1 in your set analysis would show all the records.... Maybe you should remove that 1 from your set expression?

kathrynkvas
New Contributor II

Re: KPI Drill Down from Overall to Groups to Determine % of Revenue for Products

I removed the {1<wMonth={October} parameter, however, the Sum(wRevenue)/ sum(TOTAL wRevenue). Total functions totals all the products versus the totaling the revenue for a product group. Going back to the example use case below. 


I have 50 total products. I dill down and and select 20 products (i.e. product group). I then drill down a step further to five products. I then want to get the % of revenue for those 5 products out of the 20 products I selected.


arvind654
Honored Contributor II

Re: KPI Drill Down from Overall to Groups to Determine % of Revenue for Products

Only 5 products?

No other products or selection should be valid?

kathrynkvas
New Contributor II

Re: KPI Drill Down from Overall to Groups to Determine % of Revenue for Products

Correct:

I have 5 products within a product group.

I want to see the the revenue % total of those five product in comparison to the total product group % of Total Revenue. Filtering use case:

1. No filter shows 100% of Total Revenue (all products)

2. Filter to product group - KPI shows 100% of Total Revenue to reflect that selection depicts total revenue for that particular product group.

3. Select 5 products within the product group - KPI shows % total of revenue of product with the total % of the product group.

Note - The filter does not have to be on specific poduct group (e.g. Only filter by product group A). I would like it to be nonlinear allowing the end user to select a product group f their choosing.

sum([Revenue])/ *something to filter product group* sum(Revenue)

kathrynkvas
New Contributor II

Re: KPI Drill Down from Overall to Groups to Determine % of Revenue for Products

The issue stems from the dimension (Product Group) having an

=If(not Match([wProduct Group],

'Product Group A',

'Product Grou B',

Product Group C',

Product Group D',

), [wProduct Line])

The KPI works if I don't use the product group if(no match() equation. The issue arises when I select in a bar graph the product lines with If(No match () equation. Do you have a solution for the if(no match as a dimension with the following filter functionality listed above?

arvind654
Honored Contributor II

Re: KPI Drill Down from Overall to Groups to Determine % of Revenue for Products

Only( {$<[wProduct Group] = P({1<[wProduct Group]={‘Product Group A'}>} [wProduct Group] )>} [wProduct Line] )


Maybe try with p() possible function in Set like above?



Indirect Set Analysis for P() and E()


Can you upload sample data?

or Maybe

@stalwar1 can help you more?


MVP
MVP

Re: KPI Drill Down from Overall to Groups to Determine % of Revenue for Products

What exactly do you mean when you say this

I dill down and and select 20 products (i.e. product group).

How exactly do you drill down into 20 products? Select 20 randomly or top 20 or bottom 20? Just not sure I understand this part of your question. Would you be able to post a sample or images to show what you have?

kathrynkvas
New Contributor II

Re: KPI Drill Down from Overall to Groups to Determine % of Revenue for Products

I drill down using a filter for the product groups.

When selecting individual products for that product group I select them individual products with a top 20 and top 20 bar graph.

MVP
MVP

Re: KPI Drill Down from Overall to Groups to Determine % of Revenue for Products

Not able to follow you....What do you mean when selecting individual products for that product group? What is product group? and how does it differ from products?