Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ptl14495
New Contributor III

sum of a diferent selection

Hi ,

I think this is a tricky situation... at least for me 🙂

I need to sum the quantities of the "accessories" parts, by choosing the main reference.

Please see the example in the file attached.

 

Obrigado

Saudações de Cascais, Portugal!

 

2 Replies
lblumenfeld
Contributor

Re: sum of a diferent selection

I made a few changes to your load script.
1. In table "References Compatibility Products", I renamed "Main Reference" to "Reference Number".
2. In table "References Compatibility Products", I added a column called "Compatibility Seq". In your script, use the line
     RowNo() as Compatibility Seq. This ensures that the resulting table is sorted the way yours is in the example.
3. I also added another resident load for table "References Compatibility Products" to add a record for each of the main Reference Numbers.

Adapt this script to your database loads and run it. Then create the table. If you filter on ABC, you'll get the correct result (see screenshot). 

 

[Facts Table]:
Load * Inline [
Doc Data, Doc Number, Reference, Qty
2/1/2018, 1801, ABC, 2
3/24/2018, 1802, ABD, 10
8/6/2018, 1803, ABC, 1
12/1/2018, 1804, ABG, 4
2/1/2018, 1801, CBC, 20
3/24/2018, 1802, CBD, 50
8/6/2018, 1803, CBC, 20
12/1/2018, 1804, CBG, 20
];

[Reference Table]:
Load * Inline [
Reference No, Reference Description
ABC, PRINTER A
ABD, PRINTER A TONER
ABG, PRINTER A DRUM
CBC, PRINTER C
CBD, PRINTER C TONER
CBG, PRINTER C DRUM
];

[References Compatible Products]:
Load * Inline [
Reference No, Compatible Reference, Compatible Reference Description, Compatible Seq
ABC, ABD, PRINTER A TONER, 1
ABC, ABG, PRINTER A DRUM, 2
CBC, CBD, PRINTER C TONER, 3
CBC, CBG, PRINTER C DRUM,  4
];

[References Compatible Products]:
Concatenate Load Distinct
   [Reference No],
   [Reference No] as [Compatible Reference],
   99 as [Compatible Seq]
Resident [References Compatible Products];

 

 

Data Section
Dim: [Reference No]
Dim: [Reference Description]
Dim [Compatible Reference]     {Uncheck "Include Null Values"}
Measure: MaxString([Compatible Reference Description])
Measure: Sum(If(Reference=[Compatible Reference], Qty))

Sort Section
[Compatible Reference]: Change the sort on this to an expression. The expression is [Compatible Seq].

MVP & Luminary
MVP & Luminary

Re: sum of a diferent selection

See attached example


talk is cheap, supply exceeds demand