Category with top spend.

Hello Team,

I need to add the category with the highest spend into a text Component and also mention the spend amount.

Can you help me how can i do it?

Regards,
Sachin

Hello Sachin,

in order to solve this, you could add a variable (static value) MAX_SPEND with the following content:

MAX("Categories"."Spend")

Then, add the following query to a text component:

MAX(CASE WHEN "Categories"."Spend" = <%= MAX_SPEND %> 
THEN 'The category ' || "Categories"."Name" || ' had the highest spend (' || "Categories"."Spend" || ')' 
ELSE NULL END)

Unfortunately, this only works if the category with the highest spend is not filtered out by sheet/component or selection filters.

If you are on the IBC, you could also wait until the next release, where we release a new PQL function that allows you to index values based on a certain sorting. This can then be used to write a nice query that also works with all kinds of applied Filters.

Best,
David

Thanks David,

I will explore this option.

Regards,

Sachin

Hi David,

Categories in my case are repeated in the table.

Hence I need to first sum-up the spend under all the categories and pick the category name and spend with highest spend.

Note – I also sheet filters + component filters applied.

Regards,

Sachin

Hi,

in order to first sum up values, you can use a PU_SUM, something like:

PU_SUM("Categories", "OtherTable"."Spend")

But I dont know a solution for when you have filters applied. You’ll need to wait for the next release, then the new functionality will help.

Best
David