Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

How to calculate a dimension

Hello,

I have a data set that looks like this

Country          Product      Sales

A                     X               1000

A                     Y               100    

B                      X               200

B                      Z               2000

I want to create a dimension which displays which product is the most sold per country

the result would be: product X is the most sold for country A and product Z for country B

it needs to be a dimension, not a measure, ideally I would do this in data loading the script

thank you

LM

5 Replies
Highlighted
MVP
MVP

Data:

Load * Inline

[

Country,Product,Sales

A, X, 1000

A, Y, 100   

B, X, 200

B, Z, 2000

];

Temp:

Load Country, Product, SUM(Sales) as TotalSales Resident Data Group By Country, Product;

Left Join (Temp)

Load Country, Max(TotalSales) as MaxTotalSales Resident Temp Group By Country;

New:

Load Country, Product, 1 as MaxSalesFlag Resident Temp Where MaxTotalSales = TotalSales;

Drop Table Temp;

Highlighted
Partner
Partner

try this:

a:

load

     Country,

     Sum(Sales) as SalesMax

resident your_table

group by Country;

left join

load

     Country,

     Sales as SalesMax

     Product

resident your_table;




Highlighted
Specialist
Specialist

Main_tmp:

LOAD

    Country,

    Product,

    Sales

FROM [Cartel1.xlsx] (ooxml, embedded labels, table is Foglio1);

left join(Main_tmp)

Load

  Country,

  max(Sales) as BestSales

resident Main_tmp

  group by Country;

   

Main:

NoConcatenate

Load

    if(Sales=BestSales,1,0) as [Flag Most Sold],

    Country,

    Product,

    Sales

Resident Main_tmp;

drop Table Main_tmp;

2017-05-30 11_12_39-Qlik Sense Desktop.png

Highlighted
Creator
Creator

thank you, but this is too complicated for me and also of course my data structure is much more complicated than in the initial post, for example I have the same table on a monthly basis, so the question is actually "what is the most sold product per country in a certain month etc"

is there a way to calculate this in a visualization then, as a measure perhaps?

I was thinking using FirstSortedValue or something? I tried but it does not work, I get some "-" results for some records

thank you

Highlighted
Creator II
Creator II

Hi liviumac ,

Table1:

load * inline [

Country  ,        Product ,     Sales

A      ,               X    ,           1000

A    ,                 Y    ,           100   

B    ,                  X   ,            200

B     ,                 Z      ,         2000

];

FinalTable:

load Country,FirstSortedValue(Product,-Sales) as MaxSoldProduct resident  Table1 group by Country;

drop table Table1;

You can use MaxSoldProduct as Dimension