Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have a chart which displays gift voucher usage by store. The data model is the following:
- a voucher is issued by one store and its number is unique
- vouchers can be used partially in different stores (for example: 5€ in store A, then 10€ in store B)
I have one QV fact table with all voucher issued and another table with voucher usage. The link between the two tables is natural and based on the voucher code since it is unique.
So now, it should be possible to filter the chart based on a date range (start date and end date selected by the user). The problem is with the set analysis based on this period.
I use two variables for the dates selection: v_DateUse_1 (start date) and v_DateUse_2 (end date).
In the following screenshot the expression "Amount used" = Sum([Amount_used]) which does not take into account the date selection.
When I change the expression to = Sum({<[Date Use]={">=$(v_DateUse_1) <=$(v_DateUse_2)"}>} [Amount_used])
the chart displays no result (see screenshot below). And I don't know why.
Any idea ?
Thanks in advance .
I see there is a time fraction for Date Use, try loading Date Use with Floor to remove the time portion
Floor([Date Use]) as [Date Use]
The dates in set analysis have to be in the text form, so try with
Sum({<[Date Use]={">=$(=Date(v_DateUse_1,'DD/MM/YYYY')) <=$(=Date(v_DateUse_2,'DD/MM/YYYY'))"}>} [Amount_used])
A good tip for debugging these kind of problems is to create a straight table where you leave the label for the expression blank. The header of the table will diaplay how the $ sign expansions are evaluated like
A good place to read on this topic is
I tried it but it doesn't work. The problem concerns particularly Date Use. If I put Date Issue the expression works.
I see there is a time fraction for Date Use, try loading Date Use with Floor to remove the time portion
Floor([Date Use]) as [Date Use]
Yes, this was the reason .
Thank you !!