Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
greend21
Creator III
Creator III

ValueList Expression

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))
)))

ValueList Issue.jpg

 

Labels (2)
1 Solution

Accepted Solutions
greend21
Creator III
Creator III
Author

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)
)

View solution in original post

7 Replies
brunobertels
Specialist III
Specialist III

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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.