topic Re: Any peek-like function in chart or expression? in QlikView

Any peek-like function in chart or expression?

sifatnabil — Wed, 09 Sep 2015 07:47:18 GMT

Hi,

I have a chart that counts distinct users logging in over time. How can I have a text box/table/chart where it shows only new users logging in? i.e. were not logged in in the previous month. e.g. if 10 new users logged in in September, where new is defined as did not ever log in before (or did not log in the previous month), the chart should show 10 for September, instead of 1000 (total unique users in the month).

Re: Any peek-like function in chart or expression?

sunilkumarqv — Wed, 09 Sep 2015 07:54:04 GMT

share sample so we can try and help you

Re: Any peek-like function in chart or expression?

sunny_talwar — Wed, 09 Sep 2015 07:58:58 GMT

You can use Below/Above function on the front end. But to give a more appropriate answer, it would be best if you can share a sample.

Best,

Sunny

Re: Any peek-like function in chart or expression?

sasiparupudi1 — Wed, 09 Sep 2015 07:59:07 GMT

use above/below

above([ total ] expression [ , offset [,n ]])

Returns the value of expression evaluated with the chart's dimension values as they appear on the row above the current row within a column segment in a table or, in the case of bitmap charts, in the chart's straight table equivalent (Actually all QlikView charts have a straight table equivalent with the exception of the pivot table which has a more complex structure.).

On the first row of a column segment a NULL value will be returned, as there is no row above this one.

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column.

If the table or table equivalent has multiple vertical dimensions, the current column segment will include only rows with the same values as the current row in all dimension columns except for the column showing the last dimension in the inter field sort order. The inter field sort order for pivot tables is defined simply by the order of the dimensions from left to right. For other chart types this can be manipulated in the Chart Properties: Sort dialog.

Specifying an offset greater than 1 lets you move the evaluation of expression to rows further up the current row. A negative offset number will actually make the above function equivalent to a below function with the corresponding positive offset number. Specifying an offset of 0 will evaluate the expression on the current row. Recursive calls will return NULL.

By specifying a third parameter n greater than 1, the function will return not one but a range of n values, one for each of n table rows counting upwards from the original cell. In this form, the function can be used as an argument to any of the special Chart Range Functions.

Examples:

sum( Sales ) / above( sum( Sales ))

above( sum( Sales ), 2 )

above( total sum( Sales ))

rangeavg (above(sum(x),1,3))

returns an average of the three results of the sum(x) function evaluated on the three rows immediately above the current row.

below([ total ] expression [ , offset [,n ]])

Returns the value of expression evaluated with the chart's dimension values as they appear on the row below the current row within a column segment in a table or, in the case of bitmap charts, in the chart's straight table equivalent.

On the last row of a column segment a NULL value will be returned, as there is no row below this one.

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column.

Specifying an offset greater than 1 lets you move the evaluation of expression to rows further down the current row. A negative offset number will actually make the below function equivalent to a above function with the corresponding positive offset number. Specifying an offset of 0 will evaluate the expression on the current row. Recursive calls will return NULL.

By specifying a third parameter n greater than 1, the function will return not one but a range of n values, one for each of n table rows counting downwards from the original cell. In this form, the function can be used as an argument to any of the special Chart Range Functions.

Examples:

below( sum( Sales ))

below( sum( Sales ), 2 )

below( total sum( Sales ))

rangeavg (below(sum(x),1,3))

returns an average of the three results of the sum(x) function evaluated on the three rows immediately below the current row.

hth

Sasi

Re: Any peek-like function in chart or expression?

sifatnabil — Wed, 09 Sep 2015 09:12:55 GMT

Please see attached sample data. the qvw has this data, and I've highlighted the new users (not appearing before in the data). So my end result should be a chart (or text box) that tells me the number of "new" users per month. So it should say April = 0, May = 1, June = 2

Date	User
01/04/2014	abc
01/04/2014	mgd
01/04/2014	pqf
01/04/2014	iiv
01/04/2014	pbr
01/04/2014	dsi
01/04/2014	upfaah
01/04/2014	ukzblg
01/04/2014	ukzblg
01/04/2014	dggunv
01/04/2014	qwumaa
01/04/2014	qwumaa
01/04/2014	qwumaa
01/04/2014	qwumaa
01/05/2014	qwumaa
01/05/2014	abc
01/05/2014	mgd
01/05/2014	pqf
01/05/2014	iiv
01/05/2014	pbr
01/05/2014	dsi
01/05/2014	upfaah
01/05/2014	ukzblg
01/05/2014	ukzblg
01/05/2014	dggunv
01/05/2014	wcmekc
01/06/2014	qwumaa
01/06/2014	abc
01/06/2014	mgd
01/06/2014	pqf
01/06/2014	iiv
01/06/2014	pbr
01/06/2014	dsi
01/06/2014	upfaah
01/06/2014	ukzblg
01/06/2014	ukzblg
01/06/2014	dggunv
01/06/2014	phsmgm
01/06/2014	nahujy

Re: Any peek-like function in chart or expression?

Gabriel — Wed, 09 Sep 2015 09:32:45 GMT

Hi,

I think in this instance you will have to use combination of IF statement and ABOVE function to resolve this.

You have to check the Date & User.

Re: Any peek-like function in chart or expression?

tresB — Wed, 09 Sep 2015 09:37:20 GMT

Create Month field in the script.

Straight table:

Dim: Month

Exp: Concat({<User={"=Count(User)=1"}>} Distinct User, ';')

PFA

Re: Any peek-like function in chart or expression?

sunny_talwar — Wed, 09 Sep 2015 09:43:02 GMT

I think he is looking for a count. Modifying your expression for count:

=Count({<User={"=Count(User)=1"}>} Distinct User)

Re: Any peek-like function in chart or expression?

sifatnabil — Wed, 09 Sep 2015 09:54:04 GMT

Thanks sunindia‌ and tresesco‌ this works. Could I break down the concat into more rows? e.g.

May 2014	wcmekc
Jun 2014	nahujy;phsmgm

Should instead be:

May 2014	wcmekc
Jun 2014	nahujy
Jun 2014	phsmgm

Re: Any peek-like function in chart or expression?

sasiparupudi1 — Wed, 09 Sep 2015 09:59:14 GMT

put User as a dimension

with the expression as suggested above

Count({<User={"=Count(User)=1"}>} Distinct User)

hth

Sasi

Re: Any peek-like function in chart or expression?

sifatnabil — Wed, 09 Sep 2015 10:30:13 GMT

Hi tresesco, sunindia, SasiParupudi1‌

I just tried this on my real data but it doesn't seem to work. I used tresesco's concat to see the actual usernames coming up, and I immediately spotted Users A and B showing up under different months.

Feb	Jan	Oct
A	A	B
B	D	E
F	G	H

I used this formula:

concat( {<Username={"=Count(Username)=1"}>} Distinct

Username,'; ')

Any ideas why this might not work?

Re: Any peek-like function in chart or expression?

tresB — Wed, 09 Sep 2015 10:31:24 GMT

PFA

Re: Any peek-like function in chart or expression?

sifatnabil — Wed, 09 Sep 2015 10:50:18 GMT

Found the issue. It was due to an association with another table based on Username. Getting rid of that association made it work. Thanks all!

Re: Any peek-like function in chart or expression?

sifatnabil — Wed, 09 Sep 2015 11:23:40 GMT

Darn, I think this doesn't actually give the end result that I want. For example let's say there's a new user logging in in February, and then he next logs in in May. I would want to count him as a new user in Feb, but this logic wouldn't count him! Since in the set analysis, it's scanning *all* months, and since his count in Feb+May=2, he's not a new user in Feb (but in reality, he is a new user up until Feb!).

What the logic should be is, the user should be counted as new only if he hasn't logged in for only previous months. Could the expression be amended to get this? tresesco‌ sunindia‌ SasiParupudi1‌

Re: Any peek-like function in chart or expression?

tresB — Wed, 09 Sep 2015 12:10:38 GMT

It's probably not impossible in chart. But possibility surely going to take you to a much complex expression. therefore, better to handle such situations in the script itself. Compare it and flag. Then in the front end use the flag to find new users.

Re: Any peek-like function in chart or expression?

sifatnabil — Thu, 10 Sep 2015 07:05:10 GMT

Hi guys, I tried using above function like this:

count(distinct {<Username={"=above(count(Username),1,rowno())=0"}>}

But this returns no data. My preferred logic would be that above() will evaluate ALL previous rows, instead of just the 1 above it. Any ideas on how to make it work? tresesco‌ SasiParupudi1‌ Gabsus04‌ sunindia‌

Re: Any peek-like function in chart or expression?

sifatnabil — Mon, 14 Sep 2015 04:33:06 GMT

You're right - looks like this is the only way to do it simply. Full solution provided here: Count if value does not exist in all previous rows