Calculate a new dimension based on a measure

[Anonymous]

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

[sunny_talwar]

You would need a script like this

Table:

LOAD

    product,

    "date",

    "Sales price",

    "Margin %"

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

(ooxml, embedded labels, table is Data);

FinalTable:

LOAD *,

  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;

LOAD *,

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

Resident Table

Order By product, date desc;

DROP Table Table;

[Anonymous]

Thank you Sunny this is the correct answer!!!