topic Re: How to count expression with minus value? in QlikView

How to count expression with minus value?

Thu, 15 Jan 2015 06:07:31 GMT

Hi all,

I'm wondering how to count stores that i had with minus value, in this case my YTD Growth value, i use

Count(

{< vYTDamt = {'<0'} >} Distinct (Stores)

)

but i had that error in modifier expression, So anyone can help me? i attach an image for detail,

Thanks a lot,

Cheers,

Re: How to count expression with minus value?

nikhilgarg — Thu, 15 Jan 2015 06:15:14 GMT

HEy,

Try like:

Count({< [YTD Growth] = {"< 0"} >} Distinct (Stores))

Re: How to count expression with minus value?

Anonymous — Thu, 15 Jan 2015 06:26:27 GMT

Hi ferdinand,

The direct use of variable (vYTDamt) in the set modifier will produce an error.

So use the field which is giving negative values and try.

Re: How to count expression with minus value?

Thu, 15 Jan 2015 06:30:23 GMT

Hi Nikhil,

it works, show the total but still not right total.. any advice? i attach image for detail

Re: How to count expression with minus value?

Thu, 15 Jan 2015 06:34:00 GMT

Hi Vignesh,

the variable contains set analysis with calculation.. its not field.. but an expression, YTD Growth is calculation:

(

sum({<Year={$(=Only(Year))},Month= {"<=$(=max({<Year={$(=Only(Year))}>} Month))"},MonthYear=>}Amt)

sum({<Year={$(=Only(Year)-1)},Month= {"<=$(=max({<Year={$(=Only(Year))}>} Month))"},MonthYear=>}Amt)

)

sum({<Year={$(=Only(Year))},Month= {"<=$(=max({<Year={$(=Only(Year))}>} Month))"},MonthYear=>}Amt)

thats the vYTDamt contains, any advice?

Thank you

Re: How to count expression with minus value?

Anonymous — Thu, 15 Jan 2015 06:41:47 GMT

The expr needs to be modified as below.

(

sum({<Year={$(=Only(Year))},Month= {"<=$(=max({<Year={$(=Only(Year))}>} Month))"},MonthYear=>}Amt)

sum({<Year={$(=Only(Year)-1)},Month= {"<=$(=max({<Year={$(=Only(Year)-1)}>} Month))"},MonthYear=>}Amt)

)

sum({<Year={$(=Only(Year)-1)},Month= {"<=$(=max({<Year={$(=Only(Year)-1)}>} Month))"},MonthYear=>}Amt)

And use the Count (stores) expression like :

Count({< [YTD growth] = {'<0'} >} Distinct (Stores))

Hope this helps

Thanks,

Vignesh

Re: How to count expression with minus value?

nikhilgarg — Thu, 15 Jan 2015 07:06:11 GMT

Hey,

Then what should be the total according to you ??

You want to count the distinct stores right ?

Re: How to count expression with minus value?

Thu, 15 Jan 2015 08:14:37 GMT

I'll attach .qvw , what i mean is, i want to know how many store that had minus value YTD Growth, in the qvw i attach the total say 82, but the right total is 62.. can you help me find out what is wrong on the script?

Thank you,

Re: How to count expression with minus value?

Thu, 15 Jan 2015 08:17:03 GMT

the calculation must be CY-LY / CY , cant be modified, i'll attach qvw, the total say 82.. but the right total is 26, can you help me found out the problem?

Thank you,

Re: How to count expression with minus value?

Anonymous — Thu, 15 Jan 2015 08:27:25 GMT

Try the below query for invoice table:

Invoice:

LOAD

TGL,

NAMA_CUST,

sum(SalesInvAmount-SalesRtrAmount) as SalesAmt

Resident Invoice_temp

Group by

TGL,

NAMA_CUST;

Drop Table Invoice_temp;

Re: How to count expression with minus value?

Thu, 15 Jan 2015 08:30:52 GMT

nop.. nothing happens, total still 82..

Re: How to count expression with minus value?

Thu, 15 Jan 2015 09:06:36 GMT

Hi all,

Anyone in the forum can help me found at the problem? @_@

Re: How to count expression with minus value?

kamielrajaram — Thu, 15 Jan 2015 09:43:41 GMT

Hi,

Can you provide the document with no selections included.

Thanks

Kamiel

Re: How to count expression with minus value?

Thu, 15 Jan 2015 09:49:21 GMT

just unselect "always one selected", here you go

thank you,

Re: How to count expression with minus value?

nikhilgarg — Thu, 15 Jan 2015 10:24:42 GMT

HEy , try this:

Count(if([YTD Growth]< 0 , Stores))

May be Distinct won't work out here !!

Re: How to count expression with minus value?

vinafidalgo — Thu, 15 Jan 2015 11:42:31 GMT

Hi, I'm selecting the Year 2014 and getting 82.

The expected result is 26?

Re: How to count expression with minus value?

Thu, 15 Jan 2015 11:54:53 GMT

Nop.. its still wrong, the right value is 26.. but with your exprss is 163

Re: How to count expression with minus value?

Thu, 15 Jan 2015 11:57:54 GMT

hi Vinicus,

yeap.. thats correct, did you get the expression??

Re: How to count expression with minus value?

vinafidalgo — Thu, 15 Jan 2015 12:03:39 GMT

Not yet but I'm trying to figure it out.

Re: How to count expression with minus value?

Thu, 15 Jan 2015 14:01:37 GMT

oh boy.. its hard lol, i just cant find it the right expression @_@