ValueList Expression

[greend21]

I'm using ValueList() to create a synthetic dimension and created an expression but it it is only providing output for the first value in the dimension. Does anyone know why this might be the case?

Dimension:

=ValueList('Previous Week', 'Previous Month', 'Previous 6 Months')

Expression:

=IF(ValueList('Previous Week', 'Previous Month', 'Previous 6 Months') = 'Previous Week', Avg(Aggr(Count({1<BotCategory = {'Bot Process Success'}, ProcessEndWeek = {'$(=$(vLastWeek))'}>} Distinct CashRecordID), ProcessEndDt)),

IF(ValueList('Previous Week', 'Previous Month', 'Previous 6 Months') = 'Previous Month', Avg(Aggr(Count({1<BotCategory = {'Bot Process Success'}, PrevMonthFlag = {1}>} Distinct CashRecordID), ProcessEndDt)),
IF(ValueList('Previous Week', 'Previous Month', 'Previous 6 Months') = 'Previous 6 Months', Avg(Aggr(Count({1<BotCategory = {'Bot Process Success'}, Prev6MonthsFlag = {1}>} Distinct CashRecordID), ProcessEndDt))
)))

[brunobertels]

hi 

have you tested mesures independently ?

I mean this one : 

Avg(Aggr(Count({1<BotCategory = {'Bot Process Success'}, PrevMonthFlag = {1}>} Distinct CashRecordID), ProcessEndDt) 

and this one :

Avg(Aggr(Count({1<BotCategory = {'Bot Process Success'}, Prev6MonthsFlag = {1}>} Distinct CashRecordID), ProcessEndDt)

does it work in a KPI object for example ? 

and try PrevMonthFlag = {'1'} and Prev6MonthsFlag = {'1'} may be 

[greend21]

Yes, these formulas work in independent KPIs. I tested there first and then pasted into this formula. Using '1' does not work either unfortunately.

[Saravanan_Desingh]

Replace the Expression with Pick-Match, instead of nested-if

[Saravanan_Desingh]

May be.

Pick(Match(ValueList('Previous Week', 'Previous Month', 'Previous 6 Months'),'Previous Week', 'Previous Month', 'Previous 6 Months')
	,Avg(Aggr(Count({1<BotCategory = {'Bot Process Success'}, ProcessEndWeek = {'$(=$(vLastWeek))'}>} Distinct CashRecordID), ProcessEndDt))
	,Avg(Aggr(Count({1<BotCategory = {'Bot Process Success'}, PrevMonthFlag = {1}>} Distinct CashRecordID), ProcessEndDt))
	,Avg(Aggr(Count({1<BotCategory = {'Bot Process Success'}, Prev6MonthsFlag = {1}>} Distinct CashRecordID), ProcessEndDt))
)

[greend21]

Pick(Match()) ends up with the same result. For some reason if I mix the formulas around just to see what happens it is still only the first formula that ends up calculating.

[greend21 · Accepted solution]

It looks like the issue is with using Aggr in a valuelist. Changing the formula to what I have below works fine. I left it as using Pick(Match()).

= Pick(Match(ValueList('Previous Week', 'Previous Month', 'Previous 6 Months'), 'Previous Week', 'Previous Month', 'Previous 6 Months')
,Count({1<BotCategory = {'Bot Process Success'}, ProcessEndWeek = {'$(=$(vLastWeek))'}>} Distinct CashRecordID)
/
Count({1<BotCategory = {'Bot Process Success'}, ProcessEndWeek = {'$(=$(vLastWeek))'}>}Distinct ProcessEndDt)

,Count({1<BotCategory = {'Bot Process Success'}, PrevMonthFlag = {1}>} Distinct CashRecordID)
/
Count({1<BotCategory = {'Bot Process Success'}, PrevMonthFlag = {1}>}Distinct ProcessEndDt)

,Count({1<BotCategory = {'Bot Process Success'}, Prev6MonthsFlag = {1}>} Distinct CashRecordID)
/
Count({1<BotCategory = {'Bot Process Success'}, Prev6MonthsFlag = {1}>} Distinct ProcessEndDt)
)

[Saravanan_Desingh]

Yes, you are right. Even I have faced the same issue. AGGR will not work inside ValueList Expressions. I missed it.