Trying to add a revenue bucket filter, based on current filtering view.

[Manu Lo]

Hello

I am trying to create a bucket column element that would allow me to filter on revenues made by shops.

like :

0

0 - 5

5 - 10

10 - 50

50 - 100

100+

It works more or less when no filter is applied.

But if I use a filter like country,location, product A, product B,... then bucket filter wont work anymore.

It is like Spotfire can't take into account filters when it comes to shop bucketing.

Does anyone know why? How to get around this issue?

Thanks in advance

Regards

[Gaia Paolini]

Could you give some detail about how you create the bucket column and what the other variables are like? Ideally if you could post an anonymised dxp sample, that would help clarify the issue.

[Manu Lo]

Hello, here is the latest formula details of my column elements named sales bucket:

case  WHEN sum([sales Revenues]) OVER (intersect([shop Name],[Category],[Town],[Product],[Country],[Region]))>100 THEN "100+"  WHEN sum([sales Revenues]) OVER (intersect([shop Name],[Category],[Town],[Product],[Country],[Region]))>50 THEN "50 - 100+"  WHEN sum([sales Revenues]) OVER (intersect([shop Name],[Category],[Town],[Product],[Country],[Region]))>10 THEN "10 - 50"  WHEN sum([sales Revenues]) OVER (intersect([shop Name],[Category],[Town],[Product],[Country],[Region]))>5 THEN "5 - 10"  WHEN sum([sales Revenues]) OVER (intersect([shop Name],[Category],[Town],[Product],[Country],[Region]))>0 THEN "0 - 5" ELSE "0" END

If I apply any filter and then pick only one bucket, it includes some lines that should be filtered out.

Example:

I apply no filter and want to see the shops with sales revenues between 50 and 100 => It works 🙂

I apply filters, like I want only shops located in the US with a sales revenue between 5 and 10 in Product A)

I will tick the US country, Product A and bucket 5 - 10. It doesn't work 😭

I get a list of countries with sales revenues (with shops outside of the bucket).

Unfortunately for confidentiality purposes, I am unable to provide the dxp sample.

On a personal side notes I find it sad that Tibco Spotfire teams focus a lot on Machine learning and not enough on developing the current features to design a dashboard.

There are lots of basic options missing compared to other solutions like Power BI, Tableau or even Excel. And the internal programming language is not great (could have been improved to a SQL standard like).

[Gaia Paolini]

A calculated column does not react to filters. It is always the same regardless of the filters you apply.

Maybe that is the issue. Without seeing a representative sample I cannot help more. It works with my 

randomly generated dataset.

[Manu Lo]

I would like to have a fature that allows me to select a bucket and see the shops with a revenue within that bucket.

How do I do that without a column element please?

[Douglas Johnson]

Hello Manu Lo,

Can you please build a small, simple Spotfire analysis (DXP file), with non-proprietary data embedded or stored in it, that illustrates this use case?

A concrete, sharable example will help make the discussion here more productive.

It will also help focus the discussion if you post the version of Spotfire you are using.

[Viji]

Hi Manu,

You can try creating a dropdown list and add document property with values as expressions as below

Value        Expression

100+        sum([sales Revenues])>100

50 - 100   sum([sales Revenues])>50 and  sum([sales Revenues])<100

10 -50       sum([sales Revenues])>10 and sum([sales Revenues]) <50

5 - 10      sum([sales Revenues]) >5 and sum([sales Revenues]) < 10

0 - 5   sum([sales Revenues]) >0 and sum([sales Revenues]) < 5

In the visualization data limit by expression add the document property. This is will allow you to filter with other columns and also shows the data for the sales bucket you have selected.

Thanks,

Viji