Variable as Measure in a Trend Chart

[prachisangewar]

Hi,

I have a requirement wherein I need to show a Varibale as a Measure in a Trend Chart.

The Expression for the variable is as below :

v1

= FABS(sum({<KEY = {'1_1'}>} AMOUNT1) +

sum (if(AGGR( SUM({<KEY = {'1_2'}>}AMOUNT1+ AMOUNT2),DIM1, DIM2) < 0,

aggr(sum( {<KEY = {'1_2'}>} AMOUNT1+ AMOUNT2),DIM1, DIM2))))

My Measure Expression is :

SUM({<DIM3 = {''*CAT1*}>} AMOUNT1) + v1

When view this Measure for 1 Day it gives me correct results,but if I use it in the trend report (My Trend is by Date)

it adds the total SUM of v1 for each Date

Eg :

Date                  AMOUNT1         v1   Expected Final Amount         But what I get

31-Oct-15               10                 5          10+5= 15                        10+5+7+10 = 32

30-Nov-15                15                7          15+7= 22                        15+5+7+10= 37    

31-Dec-15                18               10          18+10=28                       18+5+7+10=40

Please help to suggest,how I can change my expression to get the result

Thank you.

[sunny_talwar · Accepted solution]

Do you have the equal sign next to the expression in your variable overview? I think if you do, you need to remove it and if its not there, then try adding one .

Its like a trial and error way of doing it, but give it a shot

[prachisangewar]

Hi Sunny,

Thanks for the suggestion.

I changed the Expression the contain DATE as the third dimension in the aggregate group by , and removed '=' sign from variable overview.

New Expression:

= FABS(sum({<KEY = {'1_1'}>} AMOUNT1) +

sum (if(AGGR( SUM({<KEY = {'1_2'}>}AMOUNT1+ AMOUNT2),DIM1, DIM2,DIM3(DATE)) < 0,

aggr(sum( {<KEY = {'1_2'}>} AMOUNT1+ AMOUNT2),DIM1, DIM2,DIM3(DATE)))))