topic Re: Aggr Count distint in QlikView

Aggr Count distint

Tue, 21 Mar 2017 18:16:18 GMT

Hi guys,

I have to get an aggregate count distinct over time.

I wrote this expression:

rangesum(above(sum({<Month={'<=$(=max(Month))'}>}aggr(distinct count({<Month={'<=$(=max(Month))'}>} distinct [Goods Supplier]),[Goods Supplier])),0,RowNo()))

It works very well on my local pc (even with web view on), but it is totally messed up when published on the access point (see attached screen...this is not the shape I see on my pc).

This is a really mind blowing problem to me...I thought it was an object bug, but is seems not to be the case.

Any idea/suggestion on that?

Many thanks.

Matteo

Re: Aggr Count distint

sunny_talwar — Tue, 21 Mar 2017 18:20:00 GMT

What is wrong in the image? Can you point out?

Re: Aggr Count distint

Tue, 21 Mar 2017 18:34:15 GMT

The problem is that this can't be right (see Jan, the aggr dist is the Orange line and it should equals to the monthly value).

Re: Aggr Count distint

sunny_talwar — Wed, 22 Mar 2017 07:45:10 GMT

I sort of understand what you mean, but not really sure what might be going on.... May be someone else might have a better idea

Re: Aggr Count distint

Wed, 22 Mar 2017 10:03:12 GMT

Hope so...the biggest problem is that the chart is fine on local, while it changes significantly when published. I am not able to figure out why this happens

Re: Aggr Count distint

marcus_sommer — Wed, 22 Mar 2017 12:23:57 GMT

How looks the expression for the bars and how looked it on your local machine?

- marcus

Re: Aggr Count distint

Wed, 22 Mar 2017 14:00:43 GMT

Hi Marcus,

please find attached a snapshot.

Re: Aggr Count distint

swuehl — Wed, 22 Mar 2017 14:06:39 GMT

Does it change on the Accesspoint without reloading the data? If you reload, do you use the exact same data sources and environmental settings?

I assume your records that define relation between supplier and month come in differently (in different order) in both settings.

I would suggest that you use a different, more robuts approach for your distinct count, try an AsOf table approach:

The As-Of Table

Re: Aggr Count distint

Wed, 22 Mar 2017 14:12:50 GMT

Hi Stefan,

data sources are the same and,as far as I know , enviromental settings as well.

I actually noticed that also activating the web view the chart changes slightly shape.

Re: Aggr Count distint

swuehl — Wed, 22 Mar 2017 14:16:51 GMT

If you create a straight table with same dimension and same expressions, do these values change when you do what you've done before?

Re: Aggr Count distint

Wed, 22 Mar 2017 14:38:45 GMT

Yes, they do.

I actually have no idea on how to manage this issue.

Re: Aggr Count distint

marcus_sommer — Wed, 22 Mar 2017 14:40:24 GMT

Is section access is enabled in this application?

- Marcus

Re: Aggr Count distint

swuehl — Wed, 22 Mar 2017 14:42:26 GMT

What about the AsOf table?

This will link a month to YtD data. A count distinct should show you the correct YtD distinct suppliers then.

No hassle with the subtleties of the Aggr() function.

Re: Aggr Count distint

Wed, 22 Mar 2017 15:03:38 GMT

I have already checked this fact, and this is not the case...

Re: Aggr Count distint

Wed, 22 Mar 2017 15:30:47 GMT

Will an AsOf table work well also with uncomplete date format (YYYYMM)?

Many thanks for your collaboration!!

Re: Aggr Count distint

swuehl — Wed, 22 Mar 2017 15:39:16 GMT

Not sure what you mean with uncomplete: just using month granularity? That's shown in Henric's blog, AFAIR.

Re: Aggr Count distint

Wed, 29 Mar 2017 13:29:41 GMT

Hi Stefan,

I have already implemented the AsOf Table, but I can't figure out how to use it in my chart since I have to show not only YTD values (AsOfMonth can't be my dimension).

If you look at the picture I posted, in fact the blue bar chart shows monthly values, while the Yellow line is for the YTD ones.

Re: Aggr Count distint

swuehl — Wed, 29 Mar 2017 13:41:01 GMT

That should be explained in Henric's blog post, too:

"And finally, if you use

Sum({$<MonthDiff={0}>} Sales)

You will get the real, non-accumulated numbers."

Re: Aggr Count distint

Wed, 29 Mar 2017 13:50:43 GMT

Hi Stefan,

thanks for your support.

I have already tried to use the set analysis as shown by Henric, unluckily it seems not to work in my case.

Here the expressions (AsOfMonth as the unique dimension):

YTD: num(count(distinct [Goods Supplier]),'#.##0')

MONTHLY: num(count({$<MonthDiff={0}>} distinct [Goods Supplier]),'#.##0')

I get the same result...

Please note that the minimum granularity of my data is YYYYMM (i.e. I am not given days): could this fact be the problem?

Re: Aggr Count distint

swuehl — Wed, 29 Mar 2017 14:03:56 GMT

Doubly check that your AsOf table correctly links the AsOfMonth to the other calendar month values.

Note that you should use a set expression like {$<YearDiff={0}>} for the YTD, if you follow Henric's approach.

I assume something gets wrong when you calculate the link table. Try to create table box with your month field, the AsOfMonth field and the diff flag fields. Check for some AsOfMonth values, that the relation to the month field and the flags are created correctly.