topic Re: Find Total price of top 5 customers in a certain time frame in App Development

Find Total price of top 5 customers in a certain time frame

lost_rabbit — Fri, 28 Apr 2023 19:00:00 GMT

I am trying to find the total price of the Top 5 customers for a date range greater than 202314

This formula works (one specific week 202314)

= sum({<Customer= {"=Rank(Sum({<ExpYrWk ={'202314'}>} Price)) <=5"} >}Price)

But this does not (weeks greater than 202314)

= sum({<Customer= {"=Rank(Sum({<ExpYrWk ={">202314"}>} Price)) <=5"} >}Price)

I guess it is not working because of using double quotes here. But I have to use it to perform search operation (>202314). Not sure how to do this.

Thanks in advance

Re: Find Total price of top 5 customers in a certain time frame

Chanty4u — Fri, 28 Apr 2023 19:08:42 GMT

Try this

= sum({<Customer= {"=Rank(Sum({<ExpYrWk ={'>202314'}>} Price)) <=5"} >}Price)

Re: Find Total price of top 5 customers in a certain time frame

lost_rabbit — Fri, 28 Apr 2023 19:17:55 GMT

Tried this already. Unfortunately it returns 0 if I replace double quotes with single quotes

Re: Find Total price of top 5 customers in a certain time frame

Chanty4u — Fri, 28 Apr 2023 19:47:49 GMT

May be thus

=sum({<Customer={"=Rank(Sum({<ExpYrWk={'>$(=max({<ExpYrWk={'>202314'}>}ExpYrWk))'}>}Price))<=5"}>}Price)

Re: Find Total price of top 5 customers in a certain time frame

lost_rabbit — Fri, 28 Apr 2023 20:17:12 GMT

Nope. Doesn't work. Returns 0

Re: Find Total price of top 5 customers in a certain time frame

Kushal_Chawda — Fri, 28 Apr 2023 20:31:52 GMT

@lost_rabbit you need to escape double quotes in inner set as you already used it in outer set like below

= sum({<Customer= {"=Rank(Sum({<ExpYrWk ={"">202314""}>} Price)) <=5"} >}Price)

Re: Find Total price of top 5 customers in a certain time frame

maxgro — Fri, 28 Apr 2023 20:52:56 GMT

You can try with [ ]

From https://community.qlik.com/t5/Design/QlikView-Quoteology/ba-p/1476029

Today, in Set Analysis, it doesn't matter whether you use single or double quotes. They both interpret the content according to bullet two below. But it should matter. There is a bug here... And one day it will get fixed.

It should be this way:

Single quotes: Literal match with field value. Case sensitive.
Double quotes: A search. The string may start with < or > for numeric searches. May start with = for expression searches. May contain wildcards. Case insensitive. Double quotes can be exchanged with [ ].

Re: Find Total price of top 5 customers in a certain time frame

lost_rabbit — Mon, 01 May 2023 12:36:13 GMT

Thanks. This worked 🙂

Re: Find Total price of top 5 customers in a certain time frame

lost_rabbit — Tue, 02 May 2023 18:30:13 GMT

@Kushal_Chawda Kindly check my follow up question here