Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
Try like:
Money( sum({<[Fiscal Date]={'>=$(=MonthStart(Max({<[Fiscal Date]>}[Fiscal Date]), ))<=$(=MonthEnd(Max({<[Fiscal Date]>}[Fiscal Date])))'}>} [Sales Revenue in (Euro)]))
I get the sum value for Mrz if I select Feb and Mrz but I need the value for Jan
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)
Thank you very much