Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
ShannaJJ27
New Contributor

Difference Brands with Multiple product sizes

Hi There,

I  am working on a project in Qlik Sense and I desperately need help.

I have a 3 Tables in Excel 

 

Table 1: is pricing for FinYear 2016

Brand , 1kg , 2kg, 500kg, 1000kg

Brand1, 50, 100, 200, 500

Brand2, 60, 50, 600, 800

 

Table2: is pricing for FinYear 2017

Brand , 1kg , 2kg, 500kg, 1000kg

Brand1, 60, 200, 400, 600

Brand2, 60, 80, 700, 900

 

Table3 : Sales Quantities

Brand, Qty, PackSize, Date

Brand1, 500, 1kg, Aug-2015

Brand2,400, 2kg, Apr-2015...

 

Also the Financial Year runs Jul-Jun 

How do I associate the tables to get the sales revenue for each financial year using the right price for the right brand and the right Pack Size?

1 Reply
Employee
Employee

Re: Difference Brands with Multiple product sizes

Try this script:
FinYear2016:
CrossTable(PackSize, Qty)
Load * Inline [
Brand , 1kg , 2kg, 500kg, 1000kg
Brand1, 60, 200, 400, 600
Brand2, 60, 80, 700, 900
];

Pricing:
Load * , '2016' as FinYear
Resident FinYear2016;

Drop table FinYear2016;

FinYear2017:
CrossTable(PackSize, Qty)
Load * Inline [
Brand , 1kg , 2kg, 500kg, 1000kg
Brand1, 60, 200, 400, 600
Brand2, 60, 80, 700, 900
];

Load *, '2017' as FinYear
Resident FinYear2017;

Drop Table FinYear2017;

SalesQuantitiesTemp:
Load * Inline [
Brand, Qty, PackSize, Date
Brand1, 500, 1kg, Aug-2015
Brand2,400, 2kg, Apr-2015
];

Join (Pricing)
Load *,
If(Month='Jan' or Month='Feb' or Month ='Mar' or Month='Apr' or Month = 'May' or Month = 'June', Year-1, Year) as FinYear;
Load *,
SubField(Date, '-', 1) as Month,
SubField(Date, '-', 2) as Year
Resident SalesQuantitiesTemp;

Drop table SalesQuantitiesTemp;
Drop fields Month, Year;