How to count expression with minus value?

[#1]

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,

[nikhilgarg]

HEy,

Try like:

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

[Anonymous]

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.

[#4]

Hi Nikhil,

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

[#5]

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

[Anonymous]

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

[nikhilgarg]

Hey,

Then what should be the total according to you ??

You want to count the distinct stores right ?       

[#8]

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,

[#9]

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,

[Anonymous]

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;