Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Calculation field value based on date changed.

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.

 

4 Replies
MVP
MVP

Re: Calculation Price field based on date changed.

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

 

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner
Partner

Re: Calculation Price field based on date changed.

Hi @jontydkpi

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

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

Partner
Partner

Re: Calculation Price field based on date changed.

Does anyone have an idea??

Partner
Partner

Re: Calculation field value based on date changed.

Hi Community, 

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