Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
reporting_neu
Creator II
Creator II

Price history in visualexpression

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:

PartNoDatePrice
1100001.01.202115,50
1100003.01.202115,50
1100004.01.202116,00
1100006.02.202116,10
1100007.02.202116,10
1100009.04.202116,10
1100010.04.202113,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:

PartNoDatePrice
1100001.01.202115,50
1100004.01.202116,00
1100006.02.202116,10
1100010.04.202113,00

 

Does anyone have any idea how I can do this?

1 Solution

Accepted Solutions
Kushal_Chawda

@reporting_neu  try below

Data:
LOAD PartNo, 
     Date, 
     Price
FROM Table.qvd;

Final:
LOAD *
where Price_Flag=1;
LOAD *,
     if((PartNo=Previous(PartNo) and Date<>Previous(Date) and Price<>Previous(Price))
     or PartNo<>Previous(PartNo),1,0) as Price_Flag
Resident Data
Order by PartNo,Date;

DROP Table Data;

DROP Field Price_Flag;

View solution in original post

13 Replies
reporting_neu
Creator II
Creator II
Author

Or can you help me with a Script in the editor?

I've tried SQL before. Unfortunately not with the desired result.

SELECT 
	part
	, min(datefield) AS  datefield
	, price
FROM Database 
GROUP BY part,  datefield, price
ORDER BY part, datefield
Kushal_Chawda

@reporting_neu  try below

Data:
LOAD PartNo, 
     Date, 
     Price
FROM Table.qvd;

Final:
LOAD *
where Price_Flag=1;
LOAD *,
     if((PartNo=Previous(PartNo) and Date<>Previous(Date) and Price<>Previous(Price))
     or PartNo<>Previous(PartNo),1,0) as Price_Flag
Resident Data
Order by PartNo,Date;

DROP Table Data;

DROP Field Price_Flag;
reporting_neu
Creator II
Creator II
Author

@Kushal_Chawda 

many thanks for your response.

I understood the logic behind the formula. But unfortunately the formula does not bring the desired result.

 

1.png

This is what the result should look like:

2.png

The formula actually only picks up the differentiated data. But somehow it doesn't do that either.

Kushal_Chawda

@reporting_neu  If you are following the exact script which is provided it should work.  Can you share your script?

reporting_neu
Creator II
Creator II
Author

@Kushal_Chawda 

I'm so sorry!

It works!!! 😀

I accidentally used the same names in the columns, which is why synthetic keys were used and the other data was also displayed. Now I have a perfect result!

 

3.png

reporting_neu
Creator II
Creator II
Author

@Kushal_Chawda 

Do you have any ideas how I can list the first value? He only checks the change in the formula. I would always need this after the first value. If you had that, everything would be perfect! 😅

reporting_neu
Creator II
Creator II
Author

@Kushal_Chawda 

My mistake. That works too.
I've just ruled out part of the formula.

//or Part<>Previous(Part)

Re-inserted, it works great!

reporting_neu
Creator II
Creator II
Author

Can you help me one more time? I would like to add customers as an additional dimension. The price history should therefore be displayed for each customer and part.

Like:

CustomerPartNoDatePrice
11100001.01.202115,50
11100004.01.202116,00
11100006.02.202116,10
11100010.04.202113,00
21100002.01.202115,60
21100004.01.202115,90
21100005.02.202116,10
21100010.04.202113,00

 

If I add the following it doesn't work, sorry.

 

 if((Customer=Previous(Customer) and PartNo=Previous(PartNo) and Date<>Previous(Date) and Price<>Previous(Price))
     or PartNo<>Previous(PartNo) or Customer<>Previous(Customer),1,0) as Price_Flag

 

 

Kushal_Chawda

@reporting_neu  try below

 if((Customer=Previous(Customer) and PartNo=Previous(PartNo) and Date<>Previous(Date) and Price<>Previous(Price))
     or (PartNo<>Previous(PartNo) or Customer<>Previous(Customer)),1,0) as Price_Flag