UNION of dimensional data (custom Region)

[#1]

Hi,

I am trying to build a straight table with 2 columns. Region, sum(sales)

I have 4 regions in the data.

I would like to create a 5th row in the table called CUSTOM REGION (say Union of Region A and Region B). In SQL based BI tools, this would be a separate UNION statement with its own filter. I looked at SET analysis, but not sure if that would help with this requirement.

If this can be done, i will expand it for more columns (Region, Category, Sales, Sales Month ago etc)

Any thoughts/pointers appreciated.

Thanks,

Bharath

[#2]

may be you can do at the script level.

table a:

load region, sales;

sql select region, sales from <table>;

left join (a)

load region, sales

sql select region as 'CUSTOM REGION' as region, sales

from <table>

where region in ('A','B');

[#3]

Hi Ramkumar,

Thanks. Guess I will have to do that if I cant find a solution to do this at document level. The thing is there are many such combinations required in dashboard and prefer to do it dynamically.

Thanks,

Bharath

[#4]

Hi Bharath,

not sure if we can do at the dashboard level as we are talking about additional rows here. another approach would be to introduce a flag at the script level say customer_region_flag have that as 1 or 0 based on your condition. in your example you load statement will have another column like

             if (region='A' or region='B',1,0) as customer_region_flag.

similarly you can have flags for your combinations and use that in your expressions.

Thanks,

Ram