topic Set analysis help required in QlikView

Set analysis help required

Mon, 03 Nov 2014 16:37:29 GMT

Hi All,

I did noticed similar some similar topic here . But does not work for me.

I am trying to get from selected Year and Month generate a date and get count of all rows (including unselected) based on calculated date. As this code will be reused many times I've placed into variable. I get values correctly when nothing is selected but when I select my date is changed and number is still wrong. Can anyone suggest me how to get round this.

My script code:

Set vSelected1YearStartDate = 'MakeDate($(=Max([Financial Year]))-2, $(=Max([Month])) + 1,1)';
Set vSelectedMonthStartDate = 'MakeDate($(=Max([Financial Year]))-1, $(=Max([Month])) ,1)';
Set vSelectedMonthEndDate = 'MonthEnd(MakeDate($(=Max([Financial Year]))-1, $(=Max([Month])),1))';

now testing label value:

=Count({1<[Member Purchasing Group]={"$(=GetFieldSelections([Purchasing Group]))"}
  , [Member Date of Joining] = {">0<=$(<=$(vSelectedMonthEndDate))"}>}
  DISTINCT [Member Code])

Now if I choose following values: Financial Year = 2015, Month=10

Variables equal to:

vSelected1YearStartDate = 01/11/2013

vSelectedMonthStartDate = 01/10/2014

vSelectedMonthEndDate = 31/10/2014

It does work with no selection or selecting current year and month but selecting a previous year figure does not change.

Please not that I have to get all rows "{1}" based on selected date parameters.

Thanks in advance for any help.

Re: Set analysis help required

maxgro — Mon, 03 Nov 2014 19:59:08 GMT

maybe

=Count({1<[Member Purchasing Group]={$(=GetFieldSelections([Member Purchasing Group]))}, [Member Date of Joining] = {">0<=$(vSelectedMonthEndDate)"}>} DISTINCT [Member Code])

=Count({1<[Member Purchasing Group]=p([Member Purchasing Group]), [Member Date of Joining] = {">0<=$(vSelectedMonthEndDate)"}>} DISTINCT [Member Code])

make a chart with the expression, remove the label and you can see the set analysis as in image (above all useful for date)

Re: Set analysis help required

Tue, 04 Nov 2014 10:03:39 GMT

Thanks, but looks like still not working. Now is affected by selection but showing incorrect figures.

Adding to chart shows also incorrect figures. Figures should accumulative by year but it is not.

Results from first query does not return any results, and second one return incorrect figures:

other selection:

The correct figure for the top one should 749 members. Or 1446 if first condition excluded (Purchasing Group).

PS: What the p() function does?