Price variation

[marygrace]

We are doing a Dashboard to show the last 5 prices diferent  that we have between all the purchase order to know identify when the price change for the products.  

I would like to know if it is possible using set analysis

[Channa]

use First Load

[marygrace]

Hi,
I do not think so that First Load help to show the last 5 distinct prices. what we want to know is when the supplier change us the prices and how much?
Can you clarify me ?

[Channa]

for the change he is inserting as new or updating records

can you share some sample data and what is expected results

[shivanisapkale]

Table A:

Load * Inline

[Sales,Date

100,1/5/2019

200,2/5/2019

];

Table B:

Load *,

if(Sales-Previous(Sales)>0,Sales-Previous(Sales),0) as difference,

Resident A;

Drop Table A;

In front end take table:

Add dimesnion as if(difference>0,Date)and in limitation select  top5

Add measure as sum(Sales)

Add second measure as sum(Difference) 

Regards,

Shivani Sapkale

[marygrace]

I attach the App, 

This table show all the orders

This is what I expect to see last 5 orders with distinct cost

[JGMDataAnalysis · Accepted solution]

I considered the possibility of selecting the number of last distinct orders per [Item] and [Cost] through a slider.

In addition, i added the dummy [Item] 60500 N to demonstrate that the measure works correctly with more than one item (attached .qvf file).

Something like this in script:

SET HidePrefix = '_';
LET vSlider = 5;

Temp:
NOCONCATENATE 
LOAD 
	RowNo() AS OrderID, *
INLINE [
        Date,		  Item,		Description,		Quantity,	Cost
        13/05/2014, 60400 N,	LICORERA TOSCANA,	1000,		1.95
        11/12/2014, 60400 N,	LICORERA TOSCANA,	1000,		1.95
        26/03/2015, 60400 N,	LICORERA TOSCANA,	1000,		2.53
        04/04/2015, 60400 N,	LICORERA TOSCANA,	1000,		2.53
        24/08/2015, 60400 N,	LICORERA TOSCANA,	1000,		1.95
        24/05/2016, 60400 N,	LICORERA TOSCANA,	1000,		2.31
        16/11/2016, 60400 N,	LICORERA TOSCANA,	1000,		2.21
        15/05/2017, 60400 N,	LICORERA TOSCANA,	1000,		2.53
        21/03/2018, 60400 N,	LICORERA TOSCANA,	2000,		2.09
        02/06/2018, 60400 N,	LICORERA TOSCANA,	2000,		2.09
        22/11/2018, 60400 N,	LICORERA TOSCANA,	1000,		2.47
        15/01/2019, 60400 N,	LICORERA TOSCANA,	1000,		2.47
        21/05/2019, 60400 N,	LICORERA TOSCANA,	2000,		2.73
        26/06/2019, 60400 N,	LICORERA TOSCANA,	1,			2.69
        26/07/2019, 60400 N,	LICORERA TOSCANA,	46,			2.20		
        13/05/2014, 60500 N,	JUGUERA TOSCANA,	1000,		1.95
        11/12/2014, 60500 N,	JUGUERA TOSCANA,	1000,		1.95
        26/03/2015, 60500 N,	JUGUERA TOSCANA,	1000,		2.53
        04/04/2015, 60500 N,	JUGUERA TOSCANA,	1000,		2.53
        24/08/2015, 60500 N,	JUGUERA TOSCANA,	1000,		1.95
        24/05/2016, 60500 N,	JUGUERA TOSCANA,	1000,		2.31
        16/11/2016, 60500 N,	JUGUERA TOSCANA,	1000,		2.21
        15/05/2017, 60500 N,	JUGUERA TOSCANA,	1000,		2.53
        21/03/2018, 60500 N,	JUGUERA TOSCANA,	2000,		2.09
        02/06/2018, 60500 N,	JUGUERA TOSCANA,	2000,		2.09
        22/11/2018, 60500 N,	JUGUERA TOSCANA,	1000,		2.47
        15/01/2019, 60500 N,	JUGUERA TOSCANA,	1000,		2.47
        21/05/2019, 60500 N,	JUGUERA TOSCANA,	2000,		2.73
        26/06/2019, 60500 N,	JUGUERA TOSCANA,	1,			2.69
        26/07/2019, 60500 N,	JUGUERA TOSCANA,	46,			2.20
];

Test:
NOCONCATENATE
LOAD *,
	If(Peek(Item) <> Item, 1,
    	RangeSum(Peek(_ItemCostNo), If(_FlagItemCost = 1, 1, 0))) AS _ItemCostNo
;
LOAD *, 
	Item & '|' & Cost AS ItemCostTemp,
    If(Exists(ItemCostTemp, Item & '|' & Cost), 0, 1) AS _FlagItemCost
RESIDENT Temp
ORDER BY Item, Date DESC;

DROP TABLE Temp;
DROP FIELD ItemCostTemp;

Measure:

Sum({<[_FlagItemCost] = {1}, [_ItemCostNo] = {"<=$(vSlider)"}>} [Cost])

[tomovangel]

Hi, I have achieved this using the following logic

FirstSortedValue(Cost,Cost) this will give you the highest Cost, loaded per cost. 

FirstSortedValue(Date,Cost) will give you the Date, in which the highest cost was sorted. 

[marygrace]

This is exactly what we need

THANKS A LOT