topic Re: Set analysis ignoring selection in QlikView

Set analysis ignoring selection

keitel2015 — Tue, 29 Sep 2015 09:28:14 GMT

Hi All,

I have issues with my set analysis in QlikView. To make it easier let’s assume that I have two fields called [Fiscal date] and [Sales revenue] with the following data sets:

[Fiscal Date] [Sales revenue]

1.Jan.2014 500€

1.Feb.2014 200€

1.Mrz.2014 600€

(...) (...)

1.Dec.2014 1000€

I want to display the current month (depend on the current selection) or sum of selected months and the previous month (also depend on the current selection). What I did is an If-condition to check if any value is selected in fiscal date. If yes then use sum([Sales revenue]) to get the correct sum for the current selection (e.g. sum over Jan and Feb). Otherwise use this set analysis (which works fine):

Money( sum({<[Fiscal Date]={'>=$(=MonthStart(Max([Fiscal Date]), ))<=$(=MonthEnd(Max([Fiscal Date])))'}>} [Sales Revenue in (Euro)]))

Now my problem: If for instance Feb and Mrz is selected then should the previous month shows the sum for Jan. I get the correct Month with set analysis but 0€ as the sum of Jan. The reason is that only Feb and Mrz is selected. If I use sum({<[Fiscal date]={‘1.Feb.2014’}>} [Sales revenue]) I get 200€ If I change this manual to sum({<[Fiscal date]={‘1.Jan.2014’}>} [Sales revenue]) I get 0€.

Therefore my question: How can I ignore the fiscal date selection and get the correct sum value for the previous month depends on the current selection?

Thanks in advance.

Re: Set analysis ignoring selection

tresB — Tue, 29 Sep 2015 12:34:51 GMT

Try like:

Money( sum({<[Fiscal Date]={'>=$(=MonthStart(Max({<[Fiscal Date]>}[Fiscal Date]), ))<=$(=MonthEnd(Max({<[Fiscal Date]>}[Fiscal Date])))'}>} [Sales Revenue in (Euro)]))

Re: Set analysis ignoring selection

keitel2015 — Tue, 29 Sep 2015 14:07:41 GMT

I get the sum value for Mrz if I select Feb and Mrz but I need the value for Jan

Re: Set analysis ignoring selection

swuehl — Tue, 29 Sep 2015 14:19:00 GMT

You need to clear selections in the other calendar field the user may select in.

Assuming you've made selections in Month, Year fields, you need to write something like

Sum({<Date = {"..."} , Month=, Year= >} Sales)

Re: Set analysis ignoring selection

keitel2015 — Tue, 29 Sep 2015 14:33:45 GMT

Thank you very much