topic Re: Set analysis based on dates in QlikView

Set analysis based on dates

edemerdjieva — Wed, 25 Nov 2020 16:16:04 GMT

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 .

Re: Set analysis based on dates

stigchel — Thu, 01 Mar 2018 11:25:04 GMT

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

Re: Set analysis based on dates

stigchel — Thu, 01 Mar 2018 11:28:09 GMT

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

Re: Set analysis based on dates

sunny_talwar — Thu, 01 Mar 2018 11:34:08 GMT

A good place to read on this topic is

Dates in Set Analysis

Re: Set analysis based on dates

edemerdjieva — Thu, 01 Mar 2018 14:43:29 GMT

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

Re: Set analysis based on dates

stigchel — Thu, 01 Mar 2018 15:02:33 GMT

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]

Re: Set analysis based on dates

edemerdjieva — Thu, 01 Mar 2018 15:22:07 GMT

Yes, this was the reason .

Thank you !!