14 Replies Latest reply: Mar 14, 2014 1:58 AM by Jonathan Dienst

# why is not ok for same expression.

the express is ok :

:   COUNT({<SHOPKEY= {"=(sum(SALES>0)"}  DISTINCT SHOPKEY)     is ok .

the same :

COUNT({<SHOPKEY= {"=(sum(year={"\$(=max(year)-1)"}SALES>0)"}  DISTINCT SHOPKEY)   isn't ok ,why ?

I want to get the number of shop which  have  sales in the the two year's same monthes .

example:

sales     201401   201301      count(result)

shop1      234        230           1

shop2      0            230           0

shop3       344         0              0

how to write  it in table?

• ###### Re: why is not ok for same expression.

Because the second should be:

COUNT({<SHOPKEY= {"=(sum(year={"\$(>=max(year)-1)"}SALES>0)"}  DISTINCT SHOPKEY)

that is >= last year

• ###### Re: why is not ok for same expression.

Hi,

You cannot use " inside "

Try to use '    ' for inner expression

• ###### Re: why is not ok for same expression.

try this:

COUNT({<SHOPKEY= {"=(sum({<year={"\$(=max(year)-1)"}>}SALES>0)"}  DISTINCT SHOPKEY)

• ###### Re: why is not ok for same expression.

yes ,I can't use inside . but this I want two year's data the same time .

• ###### Re: why is not ok for same expression.

Hi,

Try this

COUNT({<SHOPKEY= {'=(sum(year={"\$(=max(year)-1)"}SALES>0)'}  DISTINCT SHOPKEY)

OR

COUNT({<SHOPKEY= {'=(sum(year={\$(=max(year)-1)}SALES>0)'}  DISTINCT SHOPKEY)  -- For Year(numeric) there is no need of Quotes.

Regards,

jagan.

• ###### Re: why is not ok for same expression.

Hi

The above responses are all nearly there, this one fixes the bracket positions and closes the set expressions properly:

COUNT({<SHOPKEY= {"=sum({<year={'\$(=max(year) - 1')}>} SALES) > 0"}>} DISTINCT SHOPKEY)

HTH

Jonathan

• ###### Re: why is not ok for same expression.

it is ok for  last year's 'sale. but I can't get result like my example :

one :

COUNT({<SHOPKEY= {"=sum({<YEAR={'\$(=max(YEAR))'}>} SALES) > 0"}-{"=sum({<YEAR={'\$(=max(YEAR)-1)'}>} SALES) <=0"}>} DISTINCT  SHOPKEY)

second

COUNT({<SHOPKEY= {"=sum({<YEAR={'\$(=max(YEAR))'}>} SALES) > 0"},SHOPKEY={"=sum({<YEAR={'\$(=max(YEAR)-1)'}>} SALES) <=0"}>} DISTINCT  SHOPKEY)

It seems  the same sales.

• ###### Re: why is not ok for same expression.

Hi,

Try this

1st expression:

=COUNT({<SHOPKEY= {"=sum({<YEAR={'\$(=max(YEAR))'}>} SALES) > 0">}DISTINCT  SHOPKEY)

-COUNT({<SHOPKEY={"=sum({<YEAR={'\$(=max(YEAR)-1)'}>} SALES) <=0"}>} DISTINCT  SHOPKEY)

• ###### Re: why is not ok for same expression.

but some shops is counted twice because both is zero.

• ###### Re: why is not ok for same expression.

Hi,

I am not sure but try this expression

=COUNT({<SHOPKEY= {"=sum(SALES) > 0"},YEAR={'\$(=max(YEAR))'}>}DISTINCT  SHOPKEY)

• ###### Re: why is not ok for same expression.

get wrong number

• ###### Re: why is not ok for same expression.

Hi,

Can you post your sample data,

• ###### Re: why is not ok for same expression.

like example:

sales     201401   201301      count(result)

shop1      234        230           1

shop2      0            230           0

shop3       344         0              0

shop4      0             0              0

shop5    -400        234          0

• ###### Re: why is not ok for same expression.

Hi

Is the field "YEAR" or "year". QV field names are case sensitive, so these are not the same field. Set analysis simply ignores invalid field names.

HTH

Jonathan