How can I do this type of Count If expression in the script?

[jblomqvist]

Hello all,

I have the following sample data:

What I want to do is create a No of Months <>0 type of column in my table where ActualValue is not 0 for those months.

I tried doing If([Actual Value]>0, Count([Actual Value])) but it doesn't work.

Any ideas how I can create the above? I must mention my MonthYear is not in this table, it is in a calender table.

Any ideas?

[sunny_talwar]

This might work:

LOAD yourFields,

          If(ActualValue <= 0, 0, AutoNumber(ActualValue))

FROM/Resident

Order By MonthYear

Best,

Sunny

[avinashelite]

try like :

If([Actual Value]>0,rowno()) as no month

[pokassov]

Hello!

t1:

NoConcatenate

load MonthYear, ActualValue,

MonthNo,

if(Previous(MonthNo)=MonthNo,0,MonthNo) as MonthNo2;

load MonthYear, ActualValue,

rangesum(if(ActualValue>0,1,0),peek('MonthNo')) as MonthNo

Resident Your_Table

order by MonthYear;

[jagan]

Hi,

Try like this

Data:

LOAD

*,

If(ActualValue <> 0, Rangesum(Peek(ValueMonthNum), 1), Alt(Peek(ValueMonthNum), 0)) AS ValueMonthNum

FROM DataSource;

Regards,

Jagan.

[swuehl]

I think you need to clarify how your actual tables in the datamodel look like. Is ActualValue an aggregated value? How is this field connected to the calendar table?

In general, I think you can do what you want by using PEEK() function, something like

LOAD MonthYear,

         ActualValue,

         If(ActualValue, rangesum(Peek(MonthCounter),1), 0) as MonthCounter

...

FROM ...

ORDER BY MonthYear;

[sunny_talwar]

Tested it out with this script:

Table:

LOAD * Inline [

Date, ActualValue

01/02/2009, 0

01/03/2009, 0

01/03/2009, 5298

01/05/2009, 6091

01/06/2009, 6476

01/07/2009, 5786

01/08/2009, 5044

01/09/2009, 6109

01/10/2009, 6581

01/11/2009, 6257

];

Table1:

LOAD MonthName(Date) as MonthYear,

  Date,

  ActualValue,

  If(ActualValue <= 0, 0, AutoNumber(ActualValue, 'ActVal')) as [No of Months <> 0 (1)],

  If(ActualValue <= 0, 0, AutoNumber(Date, 'Date')) as [No of Months <> 0 (2)]

Resident Table

Order By Date;

DROP Table Table;

Output:

HTH

Best,

Sunny

[swuehl]

Hi Sunny,

what if ActualValue is constant, but >0?

[Peter_Cammaert]

Putting this in your script will almost immediately cause conflicts with your chart as you change its dimensions and/or expressions.

Expression

=IF (ActualValue > 0, Above([No of Months <> 0])+1, 0)

produces this straight table (I added data for february to show you that a restart works ok):

Best,

Peter

[sunny_talwar]

You are right, then I guess using Date or MonthYear would be a better option?

If(ActualValue <= 0, 0, AutoNumber(Date, 'Date')) as [No of Months <> 0 (2)]