topic why count expression need to use AddYears ? in QlikView

why count expression need to use AddYears ?

paulyeo11 — Fri, 18 Mar 2016 12:44:30 GMT

Hi All

Table 1 expression return correct value on count no of company :-

Count(distinct{$<year = {$(=Max(year)-0)}, date = {"<=$(=Date(AddYears(Max({<year={$(=Max(year))}, sales = {'*'}>} date), -0)))"}, month>}company)

Table 2 expression return correct value on compute sales :-

Sum({$<year = {$(=Max(year)-0)}, month = {"<=$(=Max({<year={$(=Max(year))}, sales = {'*'}>} month))"}>}sales/$(Columndim89)/1000)

Does any one know why table 1 count expression need to use AddYears ?

Paul

Re: why count expression need to use AddYears ?

sunny_talwar — Fri, 18 Mar 2016 12:50:56 GMT

For table 1, the expression which is enabled is the second one, which is this:

=count({$<year = {$(=max(year))},month = {"<=$(=max({<year={$(=max(year))}>} month))"}, company= {'=sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} sales)>1'}>} distinct company)

When I enable the AddYear expression, I get a count of 9. Not sure which one is correct.

Re: why count expression need to use AddYears ?

paulyeo11 — Fri, 18 Mar 2016 13:32:52 GMT

Hi Sunny

Below both expression work :-

count({$<year = {$(=max(year))},month = {"<=$(=max({<year={$(=max(year))}>} month))"}, company= {'=sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} sales)>1'}>} distinct company)

Count(distinct{$<year = {$(=Max(year)-0)}, date = {"<=$(=Date(AddYears(Max({<year={$(=Max(year))}, sales = {'*'}>} date), -0)))"}, month>}company)

But when i see you did count by invoice , the expression you share with me is below :-

Count(distinct{$<year = {$(=Max(year)-0)}, date = {"<=$(=Date(AddYears(Max({<year={$(=Max(year))}, sales = {'*'}>} date), -0)))"}, month>}inv)

So i conclude that above first expression is not require. As it is very complicated.

Then i just wonder why Count by coy or invoice need to use AddYears ?

Paul

Re: why count expression need to use AddYears ?

sunny_talwar — Fri, 18 Mar 2016 13:36:09 GMT

I am confused Paul

You are asking why Count(inv) works with AddYears and other one doesn't? Is that your question?

Re: why count expression need to use AddYears ?

paulyeo11 — Fri, 18 Mar 2016 13:51:13 GMT

Hi Sunny

My question is not count(INV) use addyears and why count(coy) don't .

My question is why count use Addyears and why compute sum(sales) don't use Addyears ?

Paul

Sent from my iPhone