Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
)))
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)
)
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
Yes, these formulas work in independent KPIs. I tested there first and then pasted into this formula. Using '1' does not work either unfortunately.
Replace the Expression with Pick-Match, instead of nested-if
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))
)
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.
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)
)
Yes, you are right. Even I have faced the same issue. AGGR will not work inside ValueList Expressions. I missed it.