Set analysis ignoring selection

[keitel2015]

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.

[tresesco]

Try like:

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

[keitel2015]

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

[swuehl · Accepted solution]

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)

[keitel2015]

Thank you very much