Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi I have below requirement
Current View
Role | Sources | Total |
Role1 | SRC1 | 100 |
SRC2 | 123 | |
Role2 | SRC3 | 456 |
SRC4 | -546 | |
Role2 | SRC5 | 124 |
SRC6 | -456 | |
SRC7 | -132 |
Please find below offset logic whihc needs to added to totals above
Offset Logic
Source | Offset |
SRC6 | SRC2 |
SRC7 | SRC2 |
SRC4 | SRC3 |
Final View should be as below
Role | Sources | Total |
Role1 | SRC1 | 100 |
SRC2 | 711 | |
Role2 | SRC3 | 1002 |
SRC4 | -546 | |
Role2 | SRC5 | 124 |
SRC6 | -456 | |
SRC7 | -132 |
SRC2=123-(-456)-(-132)
SRC3=456-(-546)
Please suggest how to implement this at UI level or Script level
Thnaks in advance
I used this script to create a lookup table that you would manually populate and adjust for each offset.
The offset contains the multiplier '-1' and maps in the offsets. The chart expression is just:
sum( [Total] * multiplier)
Sources:
LOAD Role,
Sources,
Total
FROM
(ooxml, embedded labels, table is Sheet1);
//map all sources to themselves
MappingSources:
Load
Sources,
Sources as MappingSource,
1 as Multiplier
Resident Sources;
//Add additional offsets manually
Concatenate (MappingSources)
LOAD * INLINE [
Sources, MappingSource, Multiplier
SR6, SR2, -1
SR7, SR2, -1
SR8, SR3, -1
];
Hi Jonathan,
I have created mapping table based on your suggestion.
But total is not linking correctly for the new MappingSource column. Total value is same for all the sources
Role | Sources | Total |
Role1 | SRC1 | 100 |
SRC2 | 123 | |
SRC6 | 123 | |
SRC7 | 123 | |
Role2 | SRC3 | 456 |
SRC4 | 456 | |
Role2 | SRC5 | 124 |
SRC6 | -456 | |
SRC7 | -132 |
I'm looking for following output
Role | Sources | Total |
Role1 | SRC1 | 100 |
SRC2 | 711 | |
Role2 | SRC3 | 1002 |
SRC4 | -546 | |
Role2 | SRC5 | 124 |
SRC6 | -456 | |
SRC7 | -132 |
Please suggest. Thanks in advance
are you using the qvw i provided ?