Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a chart which displays a list of vouchers and their usage. KPIs include : issue amount, amount used and remaining amount.
The goal is to display a list of vouchers used within a period of dates. Attached is an image which represents the underlaying data and the chart.
KPI expressions:
Issue amount = sum({<[Usage date]={">=$(v_UsageDateStart) <=$(v_UsageDateEnd)"}>} Issue Amount)
Amount used = sum({<[Usage date]={">=$(v_UsageDateStart) <=$(v_UsageDateEnd)"}>} Amount Used)
Remaining Amount = Issue amount - Amount used
The problem is with the "Amount Used" because it takes into account only usages occuring within the date selection. The client wants that it includes all usages per voucher occuring until the $(v_UsageDateEnd).
I tried using TOTAL in the expression but it's not working (same result as previously)
Amount used = sum(TOTAL<[Voucher Code]> {<[Usage date]={">=$(v_UsageDateStart) <=$(v_UsageDateEnd)"}>} Amount Used)
The following is not working either because it modifies the perimeter of the search :
Amount used = sum({<[Usage date]={"<=$(v_UsageDateEnd)"}>} Amount Used)
I was thinking about Aggr() function but not sure how to implement it.
Thank you in advance for the advice!
How about this...
If(Column(1) <> 0,
RangeSum(
Sum({<[Usage date] = {">=$(v_UsageDateStart)<=$(v_UsageDateEnd)"}>} [Issue Amount]),
-Sum(TOTAL <[Voucher Code]> {<[Usage date] = {"<=$(v_UsageDateEnd)"}>} [Amount Used])
))
So, for Amount Used, you want to see 4.7 + 35 for Voucher Code 111?
This is the idea but only for KPI Remaining Amount.
For voucher 111 we should have :
Used Amount (only within the search periode) = 4.7
Remaining Amount = 10.3 (50 - 35 - 4.7)
So, this should work... I think
RangeSum( Sum({<[Usage date] = {">=$(v_UsageDateStart)<=$(v_UsageDateEnd)"}>} [Issue Amount]), -Sum({<[Usage date] = {"<=$(v_UsageDateEnd)"}>} [Amount Used]) )
I tried this expression but then all usages are displayed (even those outside the date range).
Can you share a screenshot of when you use this expression?
Here it is.
May be do this
If(Column(1) <> 0, RangeSum( Sum({<[Usage date] = {">=$(v_UsageDateStart)<=$(v_UsageDateEnd)"}>} [Issue Amount]), -Sum({<[Usage date] = {"<=$(v_UsageDateEnd)"}>} [Amount Used]) ))
Now there is only the usage within the date range but remaining amount should be 0 (image attached).
I don't think I understand the problem with the most recent image... what is wrong here?