Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

hobanwashburne
Contributor

Product Mix Example

I’m really struggling with a new project (I’m new to QV and
set analysis) so I created a dumbed down example of what I am trying to
accomplish. Someone want to give it a shot?

I want to display three categories on the bar chart:

  1. The product mix of the selected customer for the
    current year
  2. “The industry standard”  = The average product mix of the Customer
    Type that the selected customer belongs to last year.
  3. “The Corporate Standard” = The average product
    mix sold to all customers last year

So if AcmeSports is selected I should see the year to date
product mix for AcmeSports, The average product mix of AcmeSports and
BobsSports (because they are both Customer Type A) from Last Year, and the
Average product mix sold to all customers from Last Year.

Bonus points if you can indicate which customers are not
performing to within 2% of the Corporate Standard in any category.

1 Solution

Accepted Solutions
pgrenier
Contributor III

Re: Re: Product Mix Example

Hello Again Scott,

Sorry about that, I've once again added a updated version of your file attached to this post. In it, you shall see that I updated the three expressions in your chart such as:

2012 Industry Standard =Avg({<Customer=,Month={"2012*"}>}Aggr(Sum({<Customer=,Month={"2012*"},ProductCategory=,CustomerType=P(CustomerType)>} Net),Customer,ProductCategory))

2012 Corporate Standard = Avg({1<Month={"2012*"}>} Aggr(Sum({1<Month={"2012*"}>} Net), Customer, ProductCategory))

2013 Customer Sales = Sum({<Month={"2013*"}>} Net)

Regards,

Philippe

5 Replies
pgrenier
Contributor III

Re: Product Mix Example

Good morning Scott,

I've added a updated version of your file attached to this post. In it, you shall see that I added two new expressions in your chart, which I switched to a Combo Chart in order to show clearly the two new measures.

Industry Standard = Avg({<Customer=>}Aggr(Sum({<Customer=,ProductCategory=,CustomerType=P(CustomerType)>} Net), Customer,ProductCategory))

Corporate Standard = Avg({1} Aggr(Sum({1} Net), Customer, ProductCategory))

Regards,

Philippe

hobanwashburne
Contributor

Re: Product Mix Example

This is sooooo close, but you didn't take into account the date ranges. Or maybe I wasn't clear enough in my instructions. The data includes monthly sales dating from 201201 thru 201304. The averages should be calculated (and displayed) using only the 2012 data whereas the customer selected should display sales only during 2013.

The intention is to compare this year's sales of any given customer to the standards established last year.

pgrenier
Contributor III

Re: Re: Product Mix Example

Hello Again Scott,

Sorry about that, I've once again added a updated version of your file attached to this post. In it, you shall see that I updated the three expressions in your chart such as:

2012 Industry Standard =Avg({<Customer=,Month={"2012*"}>}Aggr(Sum({<Customer=,Month={"2012*"},ProductCategory=,CustomerType=P(CustomerType)>} Net),Customer,ProductCategory))

2012 Corporate Standard = Avg({1<Month={"2012*"}>} Aggr(Sum({1<Month={"2012*"}>} Net), Customer, ProductCategory))

2013 Customer Sales = Sum({<Month={"2013*"}>} Net)

Regards,

Philippe

pgrenier
Contributor III

Re: Product Mix Example

I've also updated my post under your other similar request (http://community.qlik.com/message/393852#393852) hoping it can help

hobanwashburne
Contributor

Re: Product Mix Example

Perfect!

Community Browser