topic Re: Simple Sum in QlikView

Simple Sum

Fri, 15 Jul 2011 18:43:32 GMT

sum({<feeddate={$(=addmonths(feeddate,-1))}>} total <[CSS Dimension]>TurnoverPopulationFlag)

I can't get the above expression to work. If I hardcode feeddate={'5/1/2011'}, it works. I've also tried using double quotes after looking at examples.

sum({<feeddate={"$(=addmonths(feeddate,-1))"}>} total <[CSS Dimension]>TurnoverPopulationFlag)

Can someone tell me what I'm doing wrong? Thanks!

Simple Sum

danielrozental — Fri, 15 Jul 2011 19:38:59 GMT

try with single quotes

sum({<feeddate={'$(=addmonths(feeddate,-1))'}>} total <[CSS Dimension]>TurnoverPopulationFlag)

Do you have a single possible value for feeddate?

Re: Simple Sum

Fri, 15 Jul 2011 19:43:00 GMT

Thanks. I tried with single quotes and that also gives me zeros. There are definitely values for feeddate. When I use this expression, addmonths(feeddate,-1) I get the previous month's feeddate for each month selected. Any other ideas?

Re: Simple Sum

danielrozental — Fri, 15 Jul 2011 19:47:46 GMT

try this, add a text object to the sheet and have this as the formula

='$(=addmonths(feeddate,-1))'

Re: Simple Sum

Fri, 15 Jul 2011 19:54:51 GMT

OK...I'm not really sure what I'm supposed to do. Which tab in Text Object Properties is the formula supposed to go? Also, what will adding a text object accomplish?

Re: Simple Sum

danielrozental — Fri, 15 Jul 2011 20:00:05 GMT

Yes, text object formula.

It will show you what the expression '$(=addmonths(feeddate,-1))' is resolving to.

You could also try posting a sample app, that will be easier than going back and forth with messages.

Re: Simple Sum

Fri, 15 Jul 2011 20:04:54 GMT

the text object remains blank. I tried diplaying fields and those don't appear either - =feeddate, =Range

Re: Simple Sum

Fri, 15 Jul 2011 20:05:03 GMT

the text object remains blank. I tried diplaying fields and those don't appear either - =feeddate, =Range

Re: Simple Sum

danielrozental — Fri, 15 Jul 2011 20:08:31 GMT

You will only get a value when there is just one possible option, just like with the expression you're trying to include in your set analysis.

Going back to the basics, what exactly are you trying to achieve in the graph/table?

Re: Simple Sum

Fri, 15 Jul 2011 20:27:11 GMT

for any selected date range (Apr 2011, May 2011, Jun 2011, etc), I'm trying to get the average headcount which is defined as current headcount plus prior headcount divided by 2.

((sum(TurnoverPopulationFlag) + sum({<feeddate={$(=addmonths(feeddate,-1))}>} total <[CSS Dimension]>TurnoverPopulationFlag))/2)

I need to look back at the previous month somehow. I'm confused at to why =addmonths(feeddate,-1) will display results when used alone, but doesn't work when used within the Sum.

addmonths(feeddate,-1) addmonths(feeddate,-1) addmonths(feeddate,-1) sum({<feeddate={''}>} total <[CSS Dimension]>TurnoverPopulationFlag)
//sum({<feeddate={'$(=AddMonths(feeddate,1))'}>} total <[CSS Dimension]>TurnoverPopulationFlag) sum({<feeddate={''}>} total <[CSS Dimension]>TurnoverPopulationFlag)
//sum({<feeddate={'$(=AddMonths(feeddate,1))'}>} total <[CSS Dimension]>TurnoverPopulationFlag) sum({<feeddate={''}>} total <[CSS Dimension]>TurnoverPopulationFlag)
//sum({<feeddate={'$(=AddMonths(feeddate,1))'}>} total <[CSS Dimension]>TurnoverPopulationFlag)
Jun 2011 May 2011 Apr 2011 Jun 2011 May 2011 Apr 2011
5/1/2011 4/1/2011 3/1/2011 0 0 0
5/1/2011 4/1/2011 3/1/2011 0 0 0
5/1/2011 4/1/2011 3/1/2011 0 0 0
5/1/2011 4/1/2011 3/1/2011 0 0 0
5/1/2011 4/1/2011 3/1/2011 0 0 0
5/1/2011 4/1/2011 3/1/2011 0 0 0
5/1/2011 4/1/2011 3/1/2011 0 0 0
5/1/2011 4/1/2011 3/1/2011 0 0 0
5/1/2011 4/1/2011 3/1/2011 0 0 0
5/1/2011 4/1/2011 3/1/2011 0 0 0

Simple Sum

Fri, 15 Jul 2011 20:51:15 GMT

"You will only get a value when there is just one possible option, just like with the expression you're trying to include in your set analysis."

This statement is confusing. Why won't my expression change with changes in addmonths(feeddate,-1)? It clearly changes with the periods when written alone, but within the Sum it's not getting evaluated.

Simple Sum

danielrozental — Fri, 15 Jul 2011 20:53:35 GMT

Whenever you're using $() you will only get one value. That's the way QV Works.

Seems like you need to do something else, if you let us know what it is that you need to do we might be able to help.

Re: Simple Sum

Tue, 19 Jul 2011 18:38:14 GMT

Daniel,

So one of my tasks is to update a line graph with the following Dimensions and Expressions:

Dimension: Range (values can be Q1 2010 through Q2 2011, Jan 2010 through Jun 2011)

Expressions: Annualized Vol Attrition Rates for High Performers, Effective Performers, Low Performers
(Sum ({<New_Rating={'High Performers'},
'$(iPossible)',
NEW_TERM_TYPE={'VOL_ATTRITION'}>}Terms)
/
(if((Range='YTD') or (Range='Prior YTD'),avgDiv,
 if((Range like 'Q*') and (mod(num#(date(Max(feeddate),'M')),3)=0),((num#(date(Max(feeddate),'M')))/num(mid(Range,2,1))),
  if((Range like 'Q*') and (mod(num#(date(Max(feeddate),'M')),3)<>0),(mod(num#(date(Max(feeddate),'M')),3)),avgDiv
))))*12)
/
if(SubStringCount('Jan.Feb.Mar.Apr.May.Jun.Jul.Aug.Sep.Oct.Nov.Dec',left(Range,3)) > 0, 
(
(sum({<New_Rating={'High Performers'},
'$(iPossible)'
>}TurnoverPopulationFlag)
+
sum({<New_Rating={'High Performers'},
'$(iPossible)',
Month_Num={'(=Month_Num-1)'} //I'm unable to look back at the previous month.  Is this possible?
//feeddate={"$(=date(addmonths(feeddate,-1),'M/D/YYYY'))"} // another failed variation
 //Range={"$(=date(addmonths(feeddate,-1),'MMM YYYY'))"}  //yet another failed variation
>}TurnoverPopulationFlag)
)/2),
(sum({<New_Rating={'High Performers'},
'$(iPossible)'
>}TurnoverPopulationFlag)
/
if((Range='YTD') or (Range='Prior YTD'),num#(date(Max(TOTAL feeddate),'M')),
 if((Range like 'Q*') and (mod(num#(date(Max(feeddate),'M')),3)=0),(num#(date(Max(feeddate),'M')))/num(mid(Range,2,1)),
  if((Range like 'Q*') and (mod(num#(date(Max(feeddate),'M')),3)<>0),mod(num#(date(Max(feeddate),'M')),3),avgDiv
))))
)

Based on the Expression above, my Quarterly numbers are correct. However, my Monthly numbers are incorrect, because I'm unable to look back at the previous month to do this calculation - (current headcount + prior headcount)/2

The underlying table and the line chart currently look like this. Is what I'm trying to do even possible? Thanks in advance.

Range High Performers Effective Performers Low Performers Total Voluntary
Q1 2010 7.97% 14.8% 21.0% 13.9%
Q2 2010 9.75% 13.7% 29.9% 13.6%
Q3 2010 7.23% 12.4% 26.7% 13.2%
Q4 2010 5.57% 13.7% 14.3% 11.2%
Q1 2011 3.76% 12.2% 15.9% 10.0%
Apr 2011 11.11% 13.5% 31.2% 14.1%
May 2011 11.24% 11.7% 16.2% 11.6%
Jun 2011 7.16% 12.4% 24.5% 11.0%

Re: Simple Sum

danielrozental — Tue, 19 Jul 2011 21:24:59 GMT

You should consider working on trying to simplify your expression as it is almost impossible to follow through.

Re: Simple Sum

Thu, 21 Jul 2011 13:13:19 GMT

Sorry for uploading that "crazy" code. Anyway, here is the issue I need to figure out. I have periods set up through a Range field that includes quarters Q1 2010 through Q2 2011 and months Jan 2010 through Jun 2011. The client wants the line graph to display quarters Q1 2010 through Q1 2011 followed by the months in the current quarter (Apr 2011, May 2011, and Jun 2011). This all currently works fine today as none of my calculations involve looking at prior months.

Now the calculation for monthly (in this case, Apr 2011, May 2011, Jun 2011) annualized attrition needs average headcount as part of the expression. Average headcount is defined as (current HC + prior HC)/2.

I'm able to do this using the AsOf technique, as described numerous times on the forums, if the line chart is strictly month over month. However, I need to show quarters AND months in the same graph. So my dilemma is preserving my Quarterly and Monthly range intervals, but for the months, I need to be able to get prior month data. Any ideas?