Different results with and without a Variable

[george55]

Hi all,

Have two questions.

1. When putting a formula in a variable and use the variable in the app (e.g. in a chart) and on the other hand use the same formula directly in the app, the results is different. Don't understand why.

The correct numbers are like this below (putting the formular directly in the chart-app):

When going with the variable, all numbers are the same:

This is the formular:

1-(
(
Sum(Aggr(Count(Distinct {<[X_CAL.Calldirection]={'Inbound'}>} [%CallId]), [X_EVE.HuntGroup], [X_CAL.TimeStamp.Calendar.Year], [X_CAL.TimeStamp.Calendar.Quartal], [X_CAL.TimeStamp.Calendar.Month], [X_CAL.TimeStamp.Calendar.Week], [X_CAL.TimeStamp.Calendar.Day]))
-
Sum(Aggr(Count(Distinct {<[X_CAL.Calldirection]={'Inbound'}, [X_EVE.EventType]={'TALKING'}>} [%CallId]), [X_EVE.HuntGroup], [X_CAL.TimeStamp.Calendar.Year], [X_CAL.TimeStamp.Calendar.Quartal], [X_CAL.TimeStamp.Calendar.Month], [X_CAL.TimeStamp.Calendar.Week], [X_CAL.TimeStamp.Calendar.Day]))
-
Sum(Aggr(Count(Distinct {<[X_CAL.Calldirection]={'Inbound'}, [X_EVE.EventType]={'28'}>} [%CallId]), [X_EVE.HuntGroup], [X_CAL.TimeStamp.Calendar.Year], [X_CAL.TimeStamp.Calendar.Quartal], [X_CAL.TimeStamp.Calendar.Month], [X_CAL.TimeStamp.Calendar.Week], [X_CAL.TimeStamp.Calendar.Day]))
)
/
Sum(Aggr(Count(Distinct {<[X_CAL.Calldirection]={'Inbound'}>} [%CallId]), [X_EVE.HuntGroup], [X_CAL.TimeStamp.Calendar.Year], [X_CAL.TimeStamp.Calendar.Quartal], [X_CAL.TimeStamp.Calendar.Month], [X_CAL.TimeStamp.Calendar.Week], [X_CAL.TimeStamp.Calendar.Day]))
)

2. and my second question:

is there a way to shorten all the time-dimensions?
[X_CAL.TimeStamp.Calendar.Year], [X_CAL.TimeStamp.Calendar.Quartal], [X_CAL.TimeStamp.Calendar.Month], [X_CAL.TimeStamp.Calendar.Week], [X_CAL.TimeStamp.Calendar.Day]

Have a period master-item, but don't know if it is possible to use it within a expression. so maybe I can avoid writing all the time dimensions many times.

thank you for your inputs and help.

[rwunderlich · Accepted solution]

In your variable definition, remove the leading "=".  That = causes the variable to be computed outside the chart. 

-Rob

[robert99]

is there a way to shorten all the time-dimensions?
[X_CAL.TimeStamp.Calendar.Year], [X_CAL.TimeStamp.Calendar.Quartal], [X_CAL.TimeStamp.Calendar.Month], [X_CAL.TimeStamp.Calendar.Week], [X_CAL.TimeStamp.Calendar.Day]

I don't know why you need to use an aggr.

But if there is, is there a reason why just using only [X_CAL.TimeStamp.Calendar.Day]  doesn't work

Re shortening the name. Change the "Calendar" name in the script to Cal is one option

[george55]

Hi Rob

When I remove the "=" then the variable become a string, no computation will be done. Maybe you mean to put the variable in the load script, not here (see Icon):

btw, is there a difference generating a variable in the loading script and in the app (see icon above)?

Thanks, George

[george55]

Deleting all time-dimensions except of [X_CAL.TimeStamp.Calendar.Day]  does not work (see output below).

In the x-axis (group) there is a Week-dimension (1 - 52) and line is using the Year-dimension. alternative it can be used the Month-dimension.

[rwunderlich]

When you reference the variable in the chart measure, you will need to wrap it in $() 

$(myVar)

See The Magic of Variables

-Rob

[george55]

Rob, this was the solution. Did not know the rule without the "=". Thank you.

Can you give me some advise for my second question as well?

George

[rwunderlich]

Re your second question.  You could put all those time dimensions in another variable and then use that variable in your expression variable. 

-Rob

[george55]

Thanks Rob. Will do so. 

Is it maybe possible to use instead a Master Item with drilldown timeperiod dimension? There are all needed time dimensions in bundle.

[rwunderlich]

It appears to me that you are using the calendar dimensions as aggr dimensions to effectively aggregate by Date.  Do you have a Date field such that instead of listing all the Calendar fields you could just use the Date field in the Aggr()?

X_CAL.TimeStamp.Calendar.Date

-Rob