Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have created a bar chart in which [Month-Year] is the Dimension & the expression is as shown below-
=Count(DISTINCT Aggr(If((STATUS<>'CNCL' AND COMPLETED >= MonthStart(Max(Date)) and Match(Type, 'Scheduled','Completed')),
CODE), Month, CODE, COMPLETED, SCHEDEND, Type))
Now when a selection is made in the list box "Month", the data only with respect to that month is shown in this bar chart. How do I make any selections made in month/year/[Month-Year] not effect this chart?
Using set analysis will work, but since I am using Aggr function here, not sure how to fit this in. Does anybody have an idea on this?
Thank you.
=Count({<Month>}DISTINCT Aggr(If((STATUS<>'CNCL' AND COMPLETED >= MonthStart(Max(Date)) and Match(Type, 'Scheduled','Completed')),
CODE), Month, CODE, COMPLETED, SCHEDEND, Type))
something like this could work for you.
=Count(DISTINCT Aggr({<STATUS-={'CNCL'} , COMPLETED {">=$( MonthStart(Max(Date)))"} , Type={ 'Scheduled','Completed'},Month, Year,[Month-Year] >} CODE), Month, CODE, COMPLETED, SCHEDEND, Type))
without set analysis! sorry.
May be this:
=Count({<Month, Year, MonthYear>}DISTINCT
Aggr(
If(Only({<Month, Year, MonthYear>}STATUS) <> 'CNCL' and
Only({<Month, Year, MonthYear>}COMPLETED) >= MonthStart(Max({<Month, Year, MonthYear>}Date)) and Match(Only({<Month, Year, MonthYear>}Type), 'Scheduled','Completed'),
Only({<Month, Year, MonthYear>}CODE))
, Month, CODE, COMPLETED, SCHEDEND, Type))
Try to convert entire expression in set analysis as complex if condition may give you performance issue, try below expression
Sum({<Month, Year, MonthYear>}aggr(Count(DISTINCT {<Month, Year, MonthYear,STATUS-={'CNCL'} ,COMPLETED ={">=$(=MonthStart(Max(Date)))"} ,Type={'Scheduled','Completed'}>}CODE), Month, CODE, COMPLETED, SCHEDEND, Type))