Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)
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?