Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to show a simple price history in a table and diagram.
For this I have a date and a part number as the dimension and the price. The date is linked to the master calendar.
In the raw data there are part numbers with several dates with the same price and different prices (see table).
Example:
PartNo | Date | Price |
11000 | 01.01.2021 | 15,50 |
11000 | 03.01.2021 | 15,50 |
11000 | 04.01.2021 | 16,00 |
11000 | 06.02.2021 | 16,10 |
11000 | 07.02.2021 | 16,10 |
11000 | 09.04.2021 | 16,10 |
11000 | 10.04.2021 | 13,00 |
In the price history, only one price change should be used.
If the price remains the same at another point in time, it should not be displayed.
The result should then look like this:
PartNo | Date | Price |
11000 | 01.01.2021 | 15,50 |
11000 | 04.01.2021 | 16,00 |
11000 | 06.02.2021 | 16,10 |
11000 | 10.04.2021 | 13,00 |
Does anyone have any idea how I can do this?
Sorry for not answering until today. I was on vacation.
I was able to test the supplementary formula from you. However, it doesn't work properly. This means that I get multiple values again when the customer is selected.
Customer | PartNo | Date | Price |
101 | 333 | 01.02.2021 | 38,00 |
101 | 333 | 05.04.2021 | 38,00 |
101 | 333 | 08.04.2021 | 38,00 |
But I don't find any logical flaw in your formula. Or did I miss something?
@reporting_neu share some sample with expected output. I will try to validate it
Excerpt from script:
NoConcatenate
TMP_U1:
Load
DocumentType
, Part
, Date
, Price
, Customer
From [lib://.../.../transform/Data.qvd]
(qvd)
Where DocumentTypte='R' and Price>0;
//*****************************************
NoConcatenate
TMP_U2:
Load *
Where Price_Flag=1;
Load *,
if((Customer=Previous(Customer) and Part=Previous(Part) and Date<>Previous(Date) and Price<>Previous(Price))
or (Part<>Previous(Part) or Customer<>Previous(Customer)),1,0) as Price_Flag
Resident TMP_U1
Order by Part, Date;
Drop Table TMP_U1;
Drop Field Price_Flag;
Result:
Date | Part | DocumentTyp | Price | Customer |
19.02.2021 | 100 | R | 38,00 | 170 |
07.04.2021 | 100 | R | 38,00 | 170 |
12.04.2021 | 100 | R | 38,00 | 170 |
09.06.2021 | 100 | R | 38,00 | 170 |
17.03.2021 | 100 | R | 27,00 | 160 |
18.03.2021 | 100 | R | 27,00 | 160 |
18.01.2021 | 300 | R | 14,50 | 160 |
11.02.2021 | 300 | R | 14,50 | 160 |
26.03.2021 | 300 | R | 18,00 | 160 |
26.06.2021 | 300 | R | 18,00 | 160 |
Expected output:
Date | Part | DocumentTyp | Price | Customer |
19.02.2021 | 100 | R | 38,00 | 170 |
17.03.2021 | 100 | R | 27,00 | 160 |
18.01.2021 | 300 | R | 14,50 | 160 |
26.03.2021 | 300 | R | 18,00 | 160 |
A price change should always be determined. For every part and for every customer.
I found the mistake. The mistake is the sorting.
That's right:
Order by Custom, Part, Date;
I would never have thought that the solution would be so simple 😅