
Re: Mapping Value from Dimension in another Dimension
Deepak Sharma Sep 20, 2017 8:32 AM (in response to Hannes Peiffer )Keep the same name for measure as well
it will auto associate your table
or perform Join

Re: Mapping Value from Dimension in another Dimension
Hannes Peiffer Sep 20, 2017 8:35 AM (in response to Deepak Sharma)What do you mean by keeping the same name for measure?
I can't do a join as I mentioned, sorry. It has to be on the surface with expressions


Re: Mapping Value from Dimension in another Dimension
Sunny Talwar Sep 20, 2017 8:48 AM (in response to Hannes Peiffer )No easy way to do this... but try this
SubField(Concat(DISTINCT TOTAL Aggr(NODISTINCT Sum(Kosten), Land_Bewirtung), ',', Aggr(NODISTINCT Sum(Kosten), Land_Bewirtung)), ',',
SubStringCount(Left(Concat(DISTINCT TOTAL Land_Bewirtung, ',', Aggr(NODISTINCT Sum(Kosten), Land_Bewirtung)), Index(Concat(DISTINCT TOTAL Land_Bewirtung, ',', Aggr(NODISTINCT Sum(Kosten), Land_Bewirtung)), Land)) &
If(Index(Concat(DISTINCT TOTAL Land_Bewirtung, ',', Aggr(NODISTINCT Sum(Kosten), Land_Bewirtung)), Land) > 0, ','), ','))
Have not tested with more data... but see if this works

Re: Mapping Value from Dimension in another Dimension
Hannes Peiffer Sep 20, 2017 10:39 AM (in response to Sunny Talwar )Hey Sunny,
works with the sample data, but unfortunately not with my original Cockpit :(

Re: Mapping Value from Dimension in another Dimension
Sunny Talwar Sep 20, 2017 10:42 AM (in response to Hannes Peiffer )Would you be able to share an updated sample so that I can see what might be missing?

Re: Mapping Value from Dimension in another Dimension
Hannes Peiffer Sep 20, 2017 11:18 AM (in response to Sunny Talwar )Okay, here is the original Cockpit with alienated data.
I set the labels exactly like in the sample cockpit

Re: Mapping Value from Dimension in another Dimension
Sunny Talwar Sep 20, 2017 11:26 AM (in response to Hannes Peiffer )What is the expected output here?.... I guess there is no Kosten here... how do I calculate that?

Re: Mapping Value from Dimension in another Dimension
Hannes Peiffer Sep 20, 2017 12:05 PM (in response to Sunny Talwar )Sorry Sunny, the label "Bewirtung" (second expression) is the "Kosten" from the sample data
The goal for Country(Land) "F" is:
Mapping the "Kosten" (140) to the first Dimension (red Arrow from "F" to "F"), so that for "F" is only one line with 2.708 (Umsatz) and 140 (Bewirtung)
The same for the other Countrys

Re: Mapping Value from Dimension in another Dimension
Sunny Talwar Sep 20, 2017 1:44 PM (in response to Hannes Peiffer )Try this
If(Index(Concat({<New_CALC_Hinweis = {"=Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)) > 0"}>}DISTINCT TOTAL '' & New_CALC_Hinweis & '', ':', Aggr(NODISTINCT Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)), New_CALC_Hinweis)), '' & New_SupplierAddressCountry & '') > 0,
SubField(Concat({<New_CALC_Hinweis = {"=Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)) > 0"}>}DISTINCT TOTAL Aggr(NODISTINCT Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)), New_CALC_Hinweis), ':', Aggr(NODISTINCT Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)), New_CALC_Hinweis)), ':',
SubStringCount(Left(Concat({<New_CALC_Hinweis = {"=Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)) > 0"}>}DISTINCT TOTAL New_CALC_Hinweis, ':', Aggr(NODISTINCT Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)), New_CALC_Hinweis)), Index(Concat({<New_CALC_Hinweis = {"=Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)) > 0"}>}DISTINCT TOTAL New_CALC_Hinweis, ':', Aggr(NODISTINCT Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)), New_CALC_Hinweis)), New_SupplierAddressCountry)) &
If(Index(Concat({<New_CALC_Hinweis = {"=Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)) > 0"}>}DISTINCT TOTAL New_CALC_Hinweis, ':', Aggr(NODISTINCT Sum(If(Match(New_CALC_Hinweis, 'F', 'SK', 'D', 'PL') and CALC_Einteilung = 'Lieferanten', Debitamount)), New_CALC_Hinweis)), New_SupplierAddressCountry) > 0, ':'), ':')))
I know you mentioned that you don't want to do any script changes, but since you were using Upper(DimensionName), my expressions are having a difficult time... just use this in the script and then you should be all good with the above expression

Re: Mapping Value from Dimension in another Dimension
Hannes Peiffer Sep 22, 2017 1:48 AM (in response to Sunny Talwar )Hi Sunny,
Monster expression, but it seems to work ;) Many thanks.
@Stefan. Also many thanks to you. I will try later if I can do a variation in the script








Re: Mapping Value from Dimension in another Dimension
Stefan Wühl Sep 20, 2017 2:13 PM (in response to Hannes Peiffer )I know that you were asking for a chart solution, but I think this kind of request should be solved in your model.
Maybe start with something like this added to your script (the first, simplified script)
Fact:
LOAD Land, Umsatz as Wert, 'Umsatz' as WertTyp
Resident Umsatz;
LOAD Land_Bewirtung as Land, Kosten as Wert, 'Kosten' as WertTyp
Resident Bewirtung;
to create a single fact table and then use WertTyp to filter your records.
Land Sum({<WertTyp = {'Umsatz'}>}Wert) Sum({<WertTyp = {'Kosten'}>}Wert) 530 280 A 180 0 D 100 180 F 250 100