topic Re: 6 months rolling set analysis expression not working with aggr function in QlikView

6 months rolling set analysis expression not working with aggr function

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

Hi,

avg(aggr(((Sum(a/b))*c),[emp ID],Month_Year))

This was my expression initially in the chart, then i got a requirement of 6 months rolling for that chart, i used the below set analysis expression, which is working fine in all expressions except the above one, where i have used aggr

Its not working with the aggr function.

{<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0), 'MMM-YYYY'))"} >}.

Please help me to find out how can i do 6 months rolling for this expression

Dimension - Month_Year

avg(aggr(((Sum(a/b))*c),[emp ID],Month_Year))

thanks..

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 10:18:54 GMT

this cant't work because set analysis have text values as input.

If you use >= or <= then you have to give the set analysis a format that the

Automatic Number Interpretation can convert into a numerical value.

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 10:23:13 GMT

you could try expression search instead for example:

{<Month_Year = {"=

(Month_Year>=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')))

and

(Month_Year<=$(=Date(addmonths(Max(Month_Year), 0), 'MMM-YYYY'))"})

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 10:23:43 GMT

I dont understand on what you are telling as TEXT .

(Sum({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0), 'MMM-YYYY'))"} >} a/b))

The above expression will display the sum(a/b) as rolling six months in the chart.

Whereas, if i use the same set analysis exp with aggr function

avg(aggr(((Sum({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0), 'MMM-YYYY'))"} >} a/b))*c),[emp ID],Month_Year))

Its not working.

Re: 6 months rolling set analysis expression not working with aggr function

Fri, 18 Aug 2017 10:31:47 GMT

try this

aggr(Sum({<Month_Year ={">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY'))<=$(=Date(addmonths(Max(Month_Year), 0),'MMM-YYYY'))"} >}a/b),[emp ID],Month_Year)

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 10:33:44 GMT

sorry, my mistake, try:

avg(aggr(((Sum({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0), 'MMM-YYYY'))"} >} a/b))

*only({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0), 'MMM-YYYY'))"} >} c)

),[emp ID],Month_Year))

could also be that you have to place the set modifier additionally within avg like

avg({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0), 'MMM-YYYY'))"} >}

aggr(((Sum({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0), 'MMM-YYYY'))"} >} a/b))

*only({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0), 'MMM-YYYY'))"} >} c)

),[emp ID],Month_Year))

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 10:45:34 GMT

Not a problem. Thank you.

Tried your expressions, but it is not working.

Re: 6 months rolling set analysis expression not working with aggr function

Anil_Babu_Samineni — Fri, 18 Aug 2017 10:50:38 GMT

May be this?

avg(aggr(((Sum({<Month_Year = {">=$(=Date(addmonths(Max(Month_Year), -5), 'MMM-YYYY')) <=$(=Date(addmonths(Max(Month_Year), 0), 'MMM-YYYY'))"} >} (a/b)))*c),[emp ID],Month_Year))

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 10:55:12 GMT

please prepare a sample

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 11:02:05 GMT

Thanks anil,

Its not working

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 11:03:44 GMT

and the exactly same expression is surely working without set modifiers in the same chart?

Re: 6 months rolling set analysis expression not working with aggr function

Anil_Babu_Samineni — Fri, 18 Aug 2017 11:17:01 GMT

Why? May be share application to test

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 11:33:09 GMT

Hi,

I have attached the sample application and explained also.

Please try this and let me know if you have doubts.

Thank you

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 11:34:27 GMT

Hi anil . I have attached the application

Re: 6 months rolling set analysis expression not working with aggr function

sunny_talwar — Fri, 18 Aug 2017 12:13:34 GMT

May be this

num(Avg({<Month_Year>}Aggr(((Sum({<Month_Year = {"=Date#(Only({1} Month_Year), 'MMM-YYYY') >= AddMonths(Max(TOTAL Date#(Month_Year, 'MMM-YYYY')), -5) and Date#(Only({1} Month_Year), 'MMM-YYYY') <= Max(TOTAL Date#(Month_Year, 'MMM-YYYY'))"}>}a/b))*Only({<Month_Year>} c)), empid, Month_Year)

),'#.##')

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 12:19:24 GMT

that's what I tried to explain at the beginning,your Month_Year field wasn't a dual, so it couldn't work...

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 12:30:54 GMT

... inline values create just text, ... they only create duals, that are recognized by the automatic number interpretation...

so you'll have to add the numerical representation with date#(...) either in Script or in Frontend as stalwar1‌ did it!

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 13:01:31 GMT

Thank you so much sunny.

I have again a different set of data.

I have attached the sample application. Can you pls help me out with this.

Now i have different a values and b values for one employee for the same month.

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 13:02:35 GMT

Thank you so much Robin

I have again a different set of data.

I have attached the sample application in reply to sunny. Can you pls help me out with that.

Now i have different a values and b values for one employee for the same month.

Re: 6 months rolling set analysis expression not working with aggr function

Anonymous — Fri, 18 Aug 2017 13:03:05 GMT

),'#.##')

Will this expression works now ??