QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
New Contributor III

Set analysis ignoring selection

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?

1 Solution

Accepted Solutions
MVP

Re: Set analysis ignoring selection

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)

4 Replies
MVP

Re: Set analysis ignoring selection

Try like:

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

New Contributor III

Re: Set analysis ignoring selection

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

MVP

Re: Set analysis ignoring selection

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)

New Contributor III

Re: Set analysis ignoring selection

Thank you very much