Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Thank you Sunny this is the correct answer!!!