Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $400 before Dec 31st: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
liviumac
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
MK_QSL
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;

agigliotti
Partner - Master III
Partner - Master III

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;




micheledenardi
Specialist II
Specialist II

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

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
liviumac
Creator
Creator
Author

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

scotly-victor
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