Calculation field value based on date changed.

[febridwi]

Hi everyone, 

I back to play around with QV, and I got a sample case from my team. This case about how to calculate the price value based on different date price valid. 

I've tried the suggestion from prev comments but it wasn't given the correct amount. Currently, I try to use set analysis which gets the TGL TRANSAKSI between DateFrom & DateTo, the set analysis: sum({$<[TGL TRANSAKSI]={">=$([DateFrom])"}, [TGL TRANSAKSI]={"< $([DateTo])"}>} QTY * HARGA) But that set analysis doesn't get the TGL TRANSAKSI, Is there any wrong with my set analysis? Does anyone have an idea? I'm quite stuck.  Have a good day!

I attache the qvw and the data, maybe it could help 🙂 

Regards, 

Febri.

[jonathandienst]

Use an interval match to join the prices to the fact data table:

T_Price:
LOAD * Inline
[PRODUCT, PRICE, DATEVALID
A, 1000, 01/01/2019
A, 2000, 15/01/2019
];

// Construct the match table
T_Match:
LOAD PRODUCT, 
	DATEVALID as DateFrom,	
	If(PRODUCT = Previous(PRODUCT),
		Previous(DATEVALID) - 1/86400,   // less by 1 second
		1E6  // far into the future
	) as DateTo,

	PRICE
Resident T_Price
ORDER By PRODUCT, DATEVALID DESC;

DROP Table T_PRICE;

DATA:
LOAD * Inline
[PRODUCT, QTY, DATETRANS
A, 100, 01/01/2019
A, 100, 09/01/2019
A, 100, 11/01/2019
A, 50, 16/01/2019
A, 50, 25/01/2019
];

Left Join (DATA)
IntervalMatch(DATETRANS, PRODUCT)  // extended interval match
LOAD DateFrom,
	DateTo,
	PRODUCT,
	PRICE
Resident T_Match;

//Optional cleanup of syn key
Left Join (DATA)
LOAD * Resident T_Match;

DROP Table T_Match;
DROP Field DateFrom, DateTo;

Now use PRODUCT as a dimension, Sum(QTY) and Sum(QTY*PRICE) as measures/expressions.

** Note this assumes that the from dates in the price table fully cover the dates in the fact table

[febridwi]

Hi @jonathandienst

Thank you for replying my sample case.. I'm working on it.  

update: Unfortunately, I don't get the correct answer following your suggestion.

[febridwi]

Does anyone have an idea??

[febridwi]

Hi Community, 

I updated my case, and I still can't find the correct output.  Does anyone have any idea or suggestion?