Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
use First Load
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 ?
for the change he is inserting as new or updating records
can you share some sample data and what is expected results
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
I attach the App,
This table show all the orders
This is what I expect to see last 5 orders with distinct cost
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])
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.
This is exactly what we need
THANKS A LOT