topic Re: Calculation field value based on date changed. in QlikView

Calculation field value based on date changed.

febridwi — Sat, 16 Nov 2024 04:28:16 GMT

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.

Re: Calculation Price field based on date changed.

jonathandienst — Wed, 13 Feb 2019 05:32:52 GMT

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

Re: Calculation Price field based on date changed.

febridwi — Wed, 13 Feb 2019 07:06:26 GMT

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.

Re: Calculation Price field based on date changed.

febridwi — Thu, 14 Feb 2019 03:36:00 GMT

Does anyone have an idea??

Re: Calculation field value based on date changed.

febridwi — Mon, 18 Feb 2019 02:31:53 GMT

Hi Community,

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