# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
New Contributor III

## 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.

Labels (3)

4 Replies
MVP

## Re: Calculation Price field based on date changed.

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

```T_Price:
[PRODUCT, PRICE, DATEVALID
A, 1000, 01/01/2019
A, 2000, 15/01/2019
];
// Construct the match table
T_Match:
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:
[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
DateTo,
PRODUCT,
PRICE
Resident T_Match;

//Optional cleanup of syn key
Left Join (DATA)

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
New Contributor III

## Re: Calculation Price field based on date changed.

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

New Contributor III

## Re: Calculation Price field based on date changed.

Does anyone have an idea??

New Contributor III

## 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?