Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Every one,
I have one Table like below
Load
Month,
Arrests,
Rating
From ...
Rating field contains the values like Positive,Negative. Now I need to calculate the count of Positive values from Rating field for each month
in the Data Model only.
Please help me how to write this calculation in scripting.
Thanks.
Try this?
Abc:
Load Month, Arrests, Rating From ...
NoConcatenate
Final:
Load *, Count(Rating) as Rating Resident Abc Group By Month Where Ratings = 'Positive';
Drop Table Abc;
Something like this?
NewTable:
NOCONCATENATE
LOAD Month
SUM(IF (Rating < 0, 1)) AS Negatives,
SUM(IF (Rating > 0, 1)) AS Positives,
SUM(IF (Rating = 0, 1)) AS Zeroes
FROM ...
GROUP BY Month;
Try to calculate this ways
Main:
Load
Month,
Arrests,
Rating
From ...
Load
Month,
Count(Rating) as RatingCount
Resident Main Where Rating >= 0
Group By Month;
What type of values in Rating field its numeric or String according to that your calculation differs.
Create straight table
Dimension:
Month
Expression: for positive count
=Count({<Rating ={">0"}>}Rating )
Expression: for negative count
=Count({<Rating ={"<0"}>}Rating )
Hi,
maybe this is ok:
Tab2:
NoConcatenate
Load Month,
count(DISTINCT Rating) as Positives
Resident Tab1
Where Rating='Positive'
Group by Month;
if the field doesn't contain string values like "Positive","Negative" and it has numbers just change the where condition to: Where Rating>=0
Hope it helps