Price history in visualexpression

[reporting_neu]

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?

[reporting_neu]

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?

[Kushal_Chawda]

@reporting_neu  share some sample with expected output. I will try to validate it

[reporting_neu]

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.

[reporting_neu]

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 😅