First value in above function

[#1]

Hi,

If I filter my data, the above function doesn't return the value for first row. Is there any way of getting the value of 2009-12 in yellow cell (in a dynamic way of course, not hard coding).

[marcus_sommer]

You could use for this a check from rowno():

if(rowno() = 1, count(DISTINCT iPerson), Above(count(DISTINCT iPerson)))

or for a cummulative sum:

if(Dimensionality() = 0, sum(aggr(count(DISTINCT iPerson),iMonth)),

rangesum(count(DISTINCT iPerson), Above(count(DISTINCT iPerson), 1, rowno(total))))

- Marcus

[#3]

If I understand correctly both versions return the same value (2010-01) not the previous (2009-12, which is filtered away) one for the first row.

[marcus_sommer]

Yes thats correct - if you want show the unfiltered value from last prevoius month you need something like this:

if(rowno() = 1, count({1< iMonth = {'2009-12'}>} DISTINCT iPerson), Above(count(DISTINCT iPerson)))

or more dynamically (you need a numeric time-period)

if(rowno() = 1, count({1< iMonth = {"$(=iMonth -1)"}>} DISTINCT iPerson), Above(count(DISTINCT iPerson)))

- Marcus

[#5]

If the filtered period starts from 2010-01, the second one still returns 0. But if add there "all" it works.

if(rowno() = 1, count({1< iMonth = {"$(=iMonth -1)"}>} all DISTINCT iPerson), Above(count(DISTINCT iPerson))).

However, if I only want to consider persons from A to G the problem is not solved...

[marcus_sommer · Accepted solution]

For me it worked with:

if(rowno() = 1, count({< iMonth = {'2009-12'}>} DISTINCT total iPerson), Above(count(DISTINCT iPerson)))

- Marcus

[#7]

Yes, that one works but I hoped I could find some solution that doesn't require hard coding the start date so that the period could be changed dynamically.

[marcus_sommer]

For this you need a continuous numeric MonthCounter - have a look here: Re: Howto make a variable to calculate quarters regarding year changes?

- Marcus

[#9]

Ok, thanks. I hoped there would be dynamic solution without peek (as my actual data is of couse more complex). I think I just then need to hard code the start date. Thanks!