% YTD Share of Top 20 Customers

[Haarika]

Hi All,

I want to display the YTD Share % of top 20 customers in a qliksense app and I am using the below expression.

however, this throws an error in the set analysis used in the rank function which is mentioned as 

Error in set modifier adhoc element list: ',' or ')' expected

Expression:

sum({<[Name]={"=rank(Sum({<[MY]={">=$(=date(YearStart(max([MY])),'MMM YYYY')) <=$(=(max([MY])))"}>}[Sales]))<=$(vTop)"}>}[Sales])
/
Sum({<[MY]={">=$(=date(YearStart(max([MY])),'MMM YYYY')) <=$(=(max([MY])))"}>}[Sales])

@Kushal_Chawda , @sunny_talwar Could you please suggest any solution to overcome this issue?

Many thanks !

[sunny_talwar]

Try this @Haarika 

Sum({<[Name] = {"=rank(Sum({<[MY] = {"">=$(=Date(YearStart(Max([MY])), 'MMM YYYY'))<=$(=Max([MY]))""}>} [Sales]))<=$(vTop)"}>} [Sales])
/
Sum({<[MY] = {">=$(=Date(YearStart(Max([MY])), 'MMM YYYY'))<=$(=Max([MY]))"}>} [Sales])

[Haarika]

hey @sunny_talwar , this expression doesnt throw any error, however it doesnt produce the correct result. instead of giving me YTD value it gives the overall value 😞

[sunny_talwar]

@Haarika expression looks okay to me, would you be able to create a new chart where you add name as a dimension and 

=rank(Sum({<[MY] = {">=$(=Date(YearStart(Max([MY])), 'MMM YYYY'))<=$(=Max([MY]))"}>} [Sales]))

as your expression to see what you get

[Haarika]

@sunny_talwar , this expression works perfectly fine when shown against the 'Name' dimension in a chart. but what im trying to do is to display the % YTD share of top 20 customers in a kpi object and thats where im facing this issue.

[sunny_talwar]

@Haarika I understand the issue, I am just trying to break down the expression and see what isn't working. Would you be able to provide a sample to look at this? feel free to email

[Kushal_Chawda]

@Haarika  also try below

sum({<[Name]={"=rank(Sum({<[MY]={">=$(=date(YearStart(max([MY])),'MMM YYYY'))<=$(=date(max([MY]),'MMM YYYY'))"}>}[Sales]))<=$(vTop)"}>}[Sales])
/
Sum({<[MY]={">=$(=date(YearStart(max([MY])),'MMM YYYY'))<=$(=date(max([MY]),'MMM YYYY'))"}>}[Sales])

[Haarika]

@Kushal_Chawda  doesnt work 😞

[Haarika · Accepted solution]

@sunny_talwar , the expression mentioned below works perfect. Thank you 🙂

Correct expression :

sum(Aggr(If(Rank(Sum({<[MY] = {">=$(=date(YearStart(max([MY])),'MMM YYYY')) <=$(=(max([MY])))"}>}[Sales])) <= $(vTop),Sum({<[MY] = {">=$(=date(YearStart(max([MY])),'MMM YYYY')) <=$(=(max([MY])))"}>}[Sales])), [Name]))
/
Sum({<[MY]={">=$(=date(YearStart(max([MY])),'MMM YYYY')) <=$(=(max([MY])))"}>}[Sales])

[sunny_talwar]

@Haarika I am glad this worked, but I am still unsure why the other one didn't work. Anyways I am glad you got what you wanted.