on 09-08-2015 8:51 AM
Hi,
I want to create a custom dimension as follows.
Standard Values
UK
DE
FR
Custom
UK
DE
FR
DE&FR
So I write
CASE
WHEN COUNTRY = 'UK' THEN 'UK'
WHEN COUNTRY = 'DE' THEN 'DE'
WHEN COUNTRY = 'FR' THEN 'FR'
WHEN COUNTRY IN ('DE','FR') THEN 'DE&FR'
END
As expected this custom dimension does not help to sum the measures for DE & FR.
How can I achieve this at universe level?
Thanks!
Hi,
Instead of using case statement edit the "custom dimension" click on the edit under the object properties tab.
In Query Panel, click the SQL button. Type the following SQL in SQL Viewer:
SELECT
Table.custom dimension
FROM
Table
UNION SELECT 'DE&FR'
FROM
dual
Select the Do not generate SQL before running/save option.
Save the universe and export it to the repository.
With this DE&FR is not going to relate with country DE or FR.
Amit
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sorry, I guess I wasn't clear. The "custom" is the output that I'm trying to generate with the "standard" values.
So simply, I have sales data associated with countries.
I'm trying to get an output which includes the sum of multiple countries.
And I have to handle this within IDT.
Sorry for the missing information again.
Amit,
I don't think it will work that way. If I understood well, Steve wants to display fact data for DE and FR twice.
One time by individual country, a second time by country group.
I would solve this in the report, by making a subtotal when the country is DE or FR.
This could also be solved in the universe, but I don't consider the solution very good practice. One could for instance make a derived table with something like :
select country, sum(measure1), sum(measure2) from fact
union all
select 'DE+FR', sum(measure1), sum(measure2) from fact
where country in ('DE','FR')
This way fact data is actually twice in the result set. There is of course the danger that fact data is accidently aggregated when the country dimension is not taken into the result set.
Any suggestions?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
96 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.