11 Replies Latest reply: Mar 3, 2017 4:13 AM by Giulio Andrea Toscano

# Calculate a new dimension based on a measure

Hi All,

I am not able to reproduce one analysis in Qlik, someone could help me? I attach a file with some example data.

My objective is to create the following table:

 product < 2000 < 4000 A 93% 80% B 80% 75% C 70% 93%

I basically have my fact table that have the following fields: Product, Data, Sale price, Margin. As my product have not always the same price I would like to make the average margin based on those product that ordered by Date has the sum of sales <2000 and after <4000.

Attached the excel file with Test Data and the result sheet, that is what I want to reproduce in qlik sense.

Ideally I just have to make a pivot table with product, those calculate dimensions and Margin as measure.

Any help would be appreciated,

Giulio

• ###### Re: Calculate a new dimension based on a measure

You averaged everything after reaching more than 2000 for product A? Since the condition is <4000, doesn't it needs to stop D7? instead of D8?

• ###### Re: Calculate a new dimension based on a measure

you are right sorry Sunny, so in this case, for product A, the margin for D8 should go in another cluster <6000. The same also for product C I am going to modify the file.

Thank you again.

• ###### Re: Calculate a new dimension based on a measure

How many clusters are there or is this something that will be dynamic? Also, these clusters, do you imagine them created in the script or on the front end?

• ###### Re: Calculate a new dimension based on a measure

The cluster will be static like 2000 - 4000 - 6000 -10000 - ecc. But I could consider also a dinamic solution. Ideally the best way for me will be in the front end, but if it is impossible i could consider also a previous aggregation in the script.

• ###### Re: Calculate a new dimension based on a measure

Hi Giulio,

Is this what you were after?

• ###### Re: Calculate a new dimension based on a measure

try this:

Finale:

product,

"Sales price"

FROM [lib://source/PROVA_3.xlsx]

(ooxml, embedded labels, table is Data);

Left Join(Finale)

product,

sum("Sales price") as SommeSales

Resident Finale

Group By product;

SalesTable:

"Sales price",

if(SommeSales<=4000,'<=4000','>4000') as "Sales Amount"

Resident Finale;

DROP Table Finale;

here is the result I got:

If you wanna include Margin, here is what I did:

In the script:

Finale:

product,

"Sales price",

"Margin %" AS Margin

FROM [lib://source/PROVA_3.xlsx]

(ooxml, embedded labels, table is Data);

Left Join(Finale)

product,

sum("Sales price") as SommeSales,

Avg(Margin) as AvgMargin

Resident Finale

Group By product;

SalesTable:

"Sales price",

AvgMargin,

if(SommeSales<=4000,'<=4000','>4000') as "Sales Amount"

Resident Finale;

DROP Table Finale;

Ben Salem Omar,

• ###### Re: Calculate a new dimension based on a measure

Thanks Omar, but I need another thing. I need to create a new dimension based on a measure, I need to sum the sales until they reach 2000 and then until they reach 4000 and calculate the average margin. I need to see every field completed in my table.

Anyway thank you for your time

• ###### Re: Calculate a new dimension based on a measure

Something along these lines?

Dimension

product

Aggr(

If(RangeSum(Above(Sum([Sales price]), 0, RowNo())) < 2000, Dual('< 2000', 1),

If(RangeSum(Above(Sum([Sales price]), 0, RowNo())) < 4000, Dual('< 4000', 2),

If(RangeSum(Above(Sum([Sales price]), 0, RowNo())) < 6000, Dual('< 6000', 3),

If(RangeSum(Above(Sum([Sales price]), 0, RowNo())) < 8000, Dual('< 8000', 4),

If(RangeSum(Above(Sum([Sales price]), 0, RowNo())) < 10000, Dual('< 10000', 5)))))), product, (date,(NUMERICA, DESC)))

Expression

Avg([Margin %])

• ###### Re: Calculate a new dimension based on a measure

And the MVP iiiiis : as always Thank you Sunny !
Now if I wanted to create this in the script; as I've done, what should we do?

• ###### Re: Calculate a new dimension based on a measure

You would need a script like this

Table:

product,

"date",

"Sales price",

"Margin %"

FROM [lib://Lib/PROVA_3.xlsx]

(ooxml, embedded labels, table is Data);

FinalTable:

If(CumSales < 2000, Dual('< 2000', 1),

If(CumSales < 4000, Dual('< 4000', 2),

If(CumSales < 6000, Dual('< 6000', 3),

If(CumSales < 8000, Dual('< 8000', 4),

If(CumSales < 10000, Dual('< 10000', 5)))))) as Buckets;

If(product = Previous(product), RangeSum(Peek('CumSales'), "Sales price"), "Sales price") as CumSales

Resident Table

Order By product, date desc;

DROP Table Table;

• ###### Re: Calculate a new dimension based on a measure

Thank you Sunny this is the correct answer!!!