Set analysis based on dates

[edemerdjieva]

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 .

[stigchel]

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])

[stigchel]

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

[sunny_talwar]

A good place to read on this topic is

Dates in Set Analysis

[edemerdjieva]

I tried it but it doesn't work. The problem concerns particularly Date Use. If I put Date Issue the expression works.

[stigchel · Accepted solution]

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]

[edemerdjieva]

Yes, this was the reason .

Thank you !!