topic Re: First value in above function in QlikView

First value in above function

Tue, 19 Aug 2014 08:34:19 GMT

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).

Re: First value in above function

marcus_sommer — Tue, 19 Aug 2014 12:15:08 GMT

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

Re: First value in above function

Tue, 19 Aug 2014 12:44:34 GMT

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.

Re: First value in above function

marcus_sommer — Tue, 19 Aug 2014 12:53:31 GMT

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

Re: Re: First value in above function

Tue, 19 Aug 2014 13:05:31 GMT

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...

Re: First value in above function

marcus_sommer — Tue, 19 Aug 2014 13:28:17 GMT

For me it worked with:

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

- Marcus

Re: First value in above function

Tue, 19 Aug 2014 13:36:34 GMT

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.

Re: First value in above function

marcus_sommer — Tue, 19 Aug 2014 13:44:16 GMT

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

- Marcus

Re: First value in above function

Wed, 20 Aug 2014 05:55:45 GMT

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!