Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
Hi Ferdinand,
please see attached, correct total is 26 yes?
hope that helps
Joe
HEy,
Try like:
Count({< [YTD Growth] = {"< 0"} >} Distinct (Stores))
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.
Hi Nikhil,
it works, show the total but still not right total.. any advice? i attach image for detail
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
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
Hey,
Then what should be the total according to you ??
You want to count the distinct stores right ?
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,
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,
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;