calculated fields to form a dimension

[Pomna]

Hello everyone I need help creating a dimension from multiple calculated fields. Below are the fields. 

1. Other Hours

(Sum(Total
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
//,[Work Date.autoCalendar.Month] = {"<=$1"}
,[Work Type] = {[Operations]}
,[Service Visibility] = {"Other"}
>}
Hours) )

2. Enhancement Hours

(Sum(Total
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] = {[Enhancing]}
>}
Hours) )

3. Maintenance Hours

(Sum(Total
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] = {[Maintaining]}
>}
Hours) )

4. Protecting Hours

(Sum(Total
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] ={"Protecting"}
>}
Hours) )

I need to create one dimension, Actual Hours using the above calculated dimensions. Any suggestions will be highly appreciated. Thanks

[lironbaram]

hi 

what you have are four measures that return numeric values , 

what values your dimension should display ?

it's not really clear 

[Pomna]

I need to create a bar chart using the four calculated measures as a dimension. I need to be able to create a dimension Actual Hours using the above calculated measures. Attached is the bar chart I would like to create. Thank you 

[mfarsln · Accepted solution]

Hi Ngumdo,

You need to create synthetic dimensions by using ValueList function.

Here is an example for one of your expressions;

dimension: =ValueList('Other Hours','Maintenance Hours')

expression1 (This is the value expression for the first dimension) :

=if(ValueList('Other Hours','Maintenance Hours')='Other Hours', (Sum(Total
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
//,[Work Date.autoCalendar.Month] = {"<=$1"}
,[Work Type] = {[Operations]}
,[Service Visibility] = {"Other"}
>}
Hours) ))

expression2 (This is the % expression for the first dimension) :

=if(ValueList('Other Hours','Maintenance Hours')='Other Hours', (Sum(Total
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
//,[Work Date.autoCalendar.Month] = {"<=$1"}
,[Work Type] = {[Operations]}
,[Service Visibility] = {"Other"}
>}
Hours) ) 
/ 
if(ValueList('Other Hours','Maintenance Hours')='Other Hours', (Sum(Total
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
//,[Work Date.autoCalendar.Month] = {"<=$1"}
,[Work Type] = {[Operations]}
>}
Hours) )))

Like the above example, you will need to create expressions based on your needs. And you can use header or subtitle expression for showing the actual hours value. Like;

='Total Actual Hours:' & chr(160) & (Sum(Total
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
//,[Work Date.autoCalendar.Month] = {"<=$1"}
,[Work Type] = {[Operations]}
>}
Hours) )

[Pomna]

Thank you mfarsln. 

The code for expression one worked but for some minor hitches.  I created the synthetic dimension as per your code but some of them are blank. (Please see attached document for the chart). Could you please look at the code and tell me what am doing wrong?

Dimension:

= Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours','Customer Project Hours','IS Project Hours')

Measure:

//Other Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours')='Other Hours'
,Sum({$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Work Type] = {[Operations]}
,[Service Visibility] = {'Other'}>}Hours),

//Protecting Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours',
'Customer Project Hours')='Protecting Hours'
,Sum({$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] ={"Protecting"}
>}Hours) ,

//Research Concept Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours')='Research Concept Hours'
,Sum({$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Activity Code] ={"RC"}>}Hours),

//Enhancement Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours','IS Project Hours')='Enhancement Hours'
,Sum({$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] = {[Enhancing]}>}Hours),

//Maintenance Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours',
'Customer Project Hours','IS Project Hours')='Maintenance Hours'
,Sum({$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] = {[Maintaining]}>}Hours),

//Customer Project Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours','IS Project Hours','IS Project Hours')='Customer Project Hours'
,Sum({$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Work Type] = {"Project"}
,Code4 = {"ISPC"}
//,Code = {"*ISPC*"}
,Code =- {"ISPC0019"}>}Hours),

//IS Project Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours','IS Project Hours')= 'IS Project Hours'
,Sum({$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Work Type] = {"Project"}
,[Code] = {"*ISPI*", "ISPC0019"}
//,[Service Visibility] = {"General and Infrastructure"}
//,[IS.Activity Type] = {"Enhancing"}
>}Hours))))))))

The code for expression two (that is % expression failed). Below is the code I used. 

//Other Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours')='Other Hours'
,Sum(total{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Work Type] = {[Operations]}
,[Service Visibility] = {'Other'}>}Hours))

/

If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours')='Other Hours'
,Sum(total{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Work Type] = {[Operations]}
,[Service Visibility] = {'Other'}>}Hours))

,

//Protecting Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours',
'Customer Project Hours')='Protecting Hours'
,Sum(total{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] ={"Protecting"}>}Hours))

/

//Protecting Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours',
'Customer Project Hours')='Protecting Hours'
,Sum(total{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] ={"Protecting"}>}Hours))

,

//Research Concept Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours')='Research Concept Hours'
,Sum(total{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Activity Code] ={"RC"}>}Hours))

/

//Research Concept Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours')='Research Concept Hours'
,Sum(total{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Activity Code] ={"RC"}>}Hours))

,

//Enhancement Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours','IS Project Hours')='Enhancement Hours'
,Sum(total{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] = {[Enhancing]}>}Hours))

/
//Enhancement Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours','IS Project Hours')='Enhancement Hours'
,Sum(total{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] = {[Enhancing]}>}Hours))

,

//Maintenance Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours',
'Customer Project Hours','IS Project Hours')='Maintenance Hours'
,Sum({$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] = {[Maintaining]}>}Hours))

/

//Maintenance Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours',
'Customer Project Hours','IS Project Hours')='Maintenance Hours'
,Sum({$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] = {[Maintaining]}>}Hours))

,

//Customer Project Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours','IS Project Hours','IS Project Hours')='Customer Project Hours'
,Sum(total{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Work Type] = {"Project"}
,Code4 = {"ISPC"}
//,Code = {"*ISPC*"}
,Code =- {"ISPC0019"}>}Hours))

/

//Customer Project Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours','IS Project Hours','IS Project Hours')='Customer Project Hours'
,Sum(total{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Work Type] = {"Project"}
,Code4 = {"ISPC"}
//,Code = {"*ISPC*"}
,Code =- {"ISPC0019"}>}Hours))

,

//IS Project Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours','IS Project Hours')= 'IS Project Hours'
,Sum(total{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Work Type] = {"Project"}
,[Code] = {"*ISPI*", "ISPC0019"}>}Hours))

/

//IS Project Hours
If(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Enhancement Hours','Maintenance Hours'
,'Customer Project Hours','IS Project Hours')= 'IS Project Hours'
,Sum(total{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Work Type] = {"Project"}
,[Code] = {"*ISPI*", "ISPC0019"}>}Hours))

The title expression code worked.

Thank you.

[mfarsln]

You are dividing values to same values itself. You need to divide them to the total value in order to get rates. If you want to see the percent of header value for each segment, then you should use the same formula, which you used in header section, as a divider.

Can you share a sample qvf file so maybe i can help you further?

[Pomna]

Thank you Mfarsln. I finally got it! Below is the code that worked for the % expression. The first part of the code gave me the first expression and divided by the total to get the % expression. Thank you!

((PICK(MATCH(Valuelist('Other Hours','Protecting Hours','Research Concept Hours','Customer Project Hours','Enhancement Hours','IS Project Hours','Maintenance Hours')
,'Other Hours','Protecting Hours','Research Concept Hours','Customer Project Hours','Enhancement Hours','IS Project Hours','Maintenance Hours'),

//Other Hours
Sum(
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
//,[Work Date.autoCalendar.Month] = {"<=$1"}
,[Work Type] = {[Operations]}
,[Service Visibility] = {"Other"}
>}
Hours)
,
//Protecting Hours
Sum(
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] ={"Protecting"}
>}
Hours) ,

// Research Concept Hours
Sum(
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Activity Code] ={"RC"}
>}
Hours),

//Customer Project Hours

Sum(
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Work Type] = {"Project"}
,Code4 = {"ISPC"}
//,Code = {"*ISPC*"}
,Code =- {"ISPC0019"}>}Hours),

//Enhancement Hours
Sum({$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] = {[Enhancing]}
,[Supported Location] =- {'04'}>}Hours),

//IS Project Hours

Sum({$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Work Type] = {"Project"}
,[Code] = {"*ISPI*", "ISPC0019"}
//,[Service Visibility] = {"General and Infrastructure"}
//,[IS.Activity Type] = {"Enhancing"}
>}Hours),

//Maintenance Hours

Sum({$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] = {'Maintaining'}
,[Supported Location] =- {'04'}
,[Activity Code] -={'RC'}>}Hours)))

/

(//Other Hours
(Sum({$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
//,[Work Date.autoCalendar.Month] = {"<=$1"}
,[Work Type] = {[Operations]}
,[Service Visibility] = {"Other"} >} Hours)

+
//Enhancing Hours
Sum(
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] = {[Enhancing]}
,[Supported Location]-={'04'}>}Hours)

+
//Maintenance Hours
Sum(
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] = {[Maintaining]}
,[Supported Location]-={'04'}
,[Activity Code] -={'RC'}>}Hours)

+

//Customer Project Hours
Sum(
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Work Type] = {"Project"}
,Code4 = {"ISPC"}
//,Code = {"*ISPC*"}
,Code =- {"ISPC0019"}>}Hours)

+
//IS Project Hours
Sum(
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Work Type] = {"Project"}
,[Code] = {"*ISPI*", "ISPC0019"}
//,[Service Visibility] = {"General and Infrastructure"}
//,[IS.Activity Type] = {"Enhancing"}
>}Hours)

+
//Protecting Hours
Sum(
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[IS.Activity Type] ={"Protecting"}
,[Supported Location]-={'04'}>}Hours)

+

//Reseach Concept Hours
(Sum(Total
{$<[Work Date.autoCalendar.Year] = {'2019'}
,[Work Date.autoCalendar.Month] = {'Jun'}
,[Activity Code] ={"RC"}>}Hours) )))

[mfarsln]

I'm glad that you made it.

Can you flag it as solution if you liked it? 🙂