Issue with getfieldselections for dates

[jerryr125]

Hi  - 

I created a KPI with the following formula to display the filter selections:

IF(GetFieldSelections([DATE.autoCalendar.Year])>0,(GetFieldSelections([DATE.autoCalendar.Year])&';'),'')&' '&
IF(GetFieldSelections([DATE.autoCalendar.YearMonth])>0,(GetFieldSelections([DATE.autoCalendar.YearMonth])&';'),'')&' '&IF(GetFieldSelections(DEPARTMENT)>0,(GetFieldSelections(DEPARTMENT)&';'),'')&' '&
IF(GetFieldSelections(CSCATEGORY)>0,(GetFieldSelections(CSCATEGORY)&';'),'')

The Filters on the sheet lets the user select  the following:

* Year (field: [DATE.autoCalendar.Year])

* Month (field: [DATE.autoCalendar.YearMonth])

* Department (field: DEPARTMENT)

* Category (field: CSCATEGORY)

The KPI will display the DEPARTMENT and CSCATEGORY  but not the Dates.

Any thoughts ?  Jerry

[Prashant_Naik]

Hi Jerry,

Rather using getfieldselection() i would suggest you to use getselectedcount() method

I used this expression and it works fine for me with dates too.

if(GetSelectedCount(Date)>0,Date(GetFieldSelections(Date)),
if(GetSelectedCount(Department)>0,GetFieldSelections(Department),
if(GetSelectedCount(Category)>0,GetFieldSelections(Category),
if(GetSelectedCount(Year)>0,Date(Date#(GetFieldSelections(Year),'YYYY'),'YYYY'))
)
)
)

Regards,

Prashant

[jerryr125]

Hi - thanks for the help - appreciate it!

I tried the above and unfortunately no luck.

I did a modification - the department and category appear but again, not the date:

IF(GetSelectedCount([DATE.autoCalendar.Year])>0,(GetFieldSelections([DATE.autoCalendar.Year])&';'),'')&' '&
IF(GetSelectedCount([DATE.autoCalendar.YearMonth])>0,(GetFieldSelections([DATE.autoCalendar.YearMonth])&';'),'')&' '&
IF(GetSelectedCount(DEPARTMENT)>0,(GetFieldSelections(DEPARTMENT)&';'),'')&' '&
IF(GetSelectedCount(CSCATEGORY)>0,(GetFieldSelections(CSCATEGORY)&';'),'')

[Prashant_Naik · Accepted solution]

Hi jerry,

i have same implemented it and it works for me, Please find the image attached.

i have used this expression.

if(GetSelectedCount(Category)>0,GetFieldSelections(Category)&';','')
&' '&if(GetSelectedCount(Department)>0,GetFieldSelections(Department)&';','')
&' '&if(GetSelectedCount(Date)>0,GetFieldSelections(Date)&';','')
&' '&if(GetSelectedCount(Year)>0,GetFieldSelections(Year)&';','')

and it worked.

i would like you to check the date format.

Regards,

Prashant