topic Re: Variable as Measure in a Trend Chart in QlikView

Variable as Measure in a Trend Chart

prachisangewar — Wed, 25 Nov 2020 16:16:04 GMT

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 :

= 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.

Re: Variable as Measure in a Trend Chart

sunny_talwar — Fri, 13 May 2016 15:50:40 GMT

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

Re: Variable as Measure in a Trend Chart

prachisangewar — Tue, 17 May 2016 09:41:31 GMT

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)))))