topic Re: why is not ok for same expression. in QlikView

why is not ok for same expression.

Thu, 13 Mar 2014 10:24:31 GMT

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.

alexandros17 — Thu, 13 Mar 2014 10:29:40 GMT

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.

PrashantSangle — Thu, 13 Mar 2014 10:38:44 GMT

Hi,

You cannot use " inside "

Try to use ' ' for inner expression

Re: why is not ok for same expression.

senpradip007 — Thu, 13 Mar 2014 10:48:09 GMT

try this:

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

Re: why is not ok for same expression.

Thu, 13 Mar 2014 10:52:52 GMT

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.

jagan — Thu, 13 Mar 2014 10:59:50 GMT

Hi,

Try this

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

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.

jonathandienst — Thu, 13 Mar 2014 11:18:36 GMT

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.

Thu, 13 Mar 2014 13:30:05 GMT

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.

PrashantSangle — Thu, 13 Mar 2014 14:09:20 GMT

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.

Thu, 13 Mar 2014 14:31:41 GMT

but some shops is counted twice because both is zero.

Re: why is not ok for same expression.

PrashantSangle — Thu, 13 Mar 2014 15:01:33 GMT

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.

Thu, 13 Mar 2014 15:50:49 GMT

get wrong number

Re: why is not ok for same expression.

PrashantSangle — Thu, 13 Mar 2014 16:19:07 GMT

Hi,

Can you post your sample data,

and what is your dimension?

Re: why is not ok for same expression.

Fri, 14 Mar 2014 01:50:40 GMT

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.

jonathandienst — Fri, 14 Mar 2014 05:58:43 GMT

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