topic Re: 3 month average not working in straight table in QlikView

3 month average not working in straight table

sifatnabil — Mon, 18 Jan 2016 16:58:06 GMT

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?

Re: 3 month average not working in straight table

sifatnabil — Tue, 19 Jan 2016 09:05:26 GMT

bump

Re: 3 month average not working in straight table

HirisH_V7 — Tue, 19 Jan 2016 09:16:52 GMT

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

Re: 3 month average not working in straight table

sunny_talwar — Tue, 19 Jan 2016 09:34:55 GMT

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.

Re: 3 month average not working in straight table

jonathandienst — Tue, 19 Jan 2016 09:35:08 GMT

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.

Re: 3 month average not working in straight table

sifatnabil — Tue, 19 Jan 2016 13:03:01 GMT

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.