3 month average not working in straight table

[sifatnabil]

Hi all,

I have a straight table with dimension = CLIENT and in the expression, I'm using set analysis to get the revenue vs. 3 month average as a percentage, which is (revenue - 3m avg)/3m avg, like below:

(sum({<[Year]={'$(=max([Year]))'},[Month]={'$(=$(vCurrentMonth))'}>} [REVENUE])

-

(sum({<[Year]={'$(=max([Year]))'},[DATE]={'>=$(=monthstart(max([DATE]),-2))<=$(=monthend(max([DATE])))'}>} [REVENUE])/3))

/

(sum({<[Year]={'$(=max([Year]))'},[DATE]={'>=$(=monthstart(max([DATE]),-2))<=$(=monthend(max([DATE])))'}>} [REVENUE])/3)

But for some reason this expression ignores the dimension and gives the 3m average of ALL clients on each row. What could be wrong with this?

[sifatnabil]

bump

[HirisH_V7]

Hi ,

May be like this,

=(sum({<[Year]={'$(=max([Year]))'},[Month]={'$(=$(vCurrentMonth))'}>} [REVENUE])

-

(Avg({<[Year]={'$(=max([Year]))'},[DATE]={'>=$(=monthstart(max([DATE]),-2))<=$(=monthend(max([DATE])))'}>} [REVENUE])))

/

(Avg({<[Year]={'$(=max([Year]))'},[DATE]={'>=$(=monthstart(max([DATE]),-2))<=$(=monthend(max([DATE])))'}>} [REVENUE]))

Use this expression,

-Hirish

[sunny_talwar]

Can you may be try breaking the expression into pieces and build it together one by one. Start with a simple expression Sum(REVENUE) against the dimension Client to check if that is working or not. If you still get same number for all rows, I would think that somehow Client and Revenue fields are not linked to each other. but if they do, I will check this -> sum({<[Year]={'$(=max([Year]))'},[Month]={'$(=$(vCurrentMonth))'}>} [REVENUE]) and this -> sum({<[Year]={'$(=max([Year]))'},[DATE]={'>=$(=monthstart(max([DATE]),-2))<=$(=monthend(max([DATE])))'}>} [REVENUE])/3) as two separate expressions. and go on from there.

[jonathandienst]

I suggest that you upload your qvw or a representative sample. It looks like the problem could be that the CLIENT and REVENUE are not associated correctly.

[sifatnabil]

Sorry guys, the issue was this: [Year]={'$(=max([Year]))'}, since it's January 2016, it's not able to look back 3 months because of this restriction, hence it didn't work. Removing this worked. I'm not sure why it caused the expression to aggregate over all clients though.

Also, the client and revenue are on the same table so there aren't any issues with that.