How can I do this type of Count If expression in the script? [Better example]

[jblomqvist]

Hi all,

Sorry I don't think I explained full the extent of what I was trying to do with my Count If expression in the last thread.

The data looks like this:

No of Months <> 0 should count months where Actual Value is not 0.

In my example when it finds 12 months of value in a row then it will calculate the sum of those 12 months in the "Previous 12 months Actual Total" column.

This is based on if No of Months <> 0 = 12 at the moment.

I have two tables.

Transactions table holds CompanyCode and Actual Value.

MonthYear is held in a Calender table.

So this data will vary company by company so for another company it might look like:

Does anyone know how I can get this to work?

[sunny_talwar]

I think it would be better if you give the same data in form of Excel rather than giving us a screenshot. The only way we can test it out is if we have a sample to work with.

I hope that makes sense.

Best,

Sunny

[maxgro]

PFA

// test data

a:

load

  'ABC' as Company,

  date( addmonths(makedate(2009,2), RecNo() -1),'MMM-YY') as MonthYear,

  if(RecNo()<3 or RecNo()=21, 0, 1*recno()) as ActualValue

AutoGenerate 34;

Concatenate (a)

load

  'John' as Company,

  date( addmonths(makedate(2009,2), RecNo() -1),'MMM-YY') as MonthYear,

  if(RecNo()<3 or RecNo()=21, 0, 10*recno()) as ActualValue

AutoGenerate 34;

// end test data

b:

load

  *,

  if(ActualValue=0 or Company<>peek(Company),0,rangemin(peek(NoOfMonthsNot0)+1,12)) as NoOfMonthsNot0

Resident a

order by Company, MonthYear;

DROP Table a;

c:

load

  *,

  if(NoOfMonthsNot0=12,

  rangesum(

  Peek('ActualValue', -1),Peek('ActualValue', -2),Peek('ActualValue', -3),

  Peek('ActualValue', -4),Peek('ActualValue', -5),Peek('ActualValue', -6),

  Peek('ActualValue', -7),Peek('ActualValue', -8),Peek('ActualValue', -9),

  Peek('ActualValue', -10),Peek('ActualValue', -11),ActualValue

  ), 0) as Prev12MonthsValue

Resident b

order by Company, MonthYear;

drop Table b;

[jblomqvist]

You are right sunindia I will do this soon and submit it!

[jblomqvist]

I have attached the sample data and what I am trying to do in the original message. Thanks 

[sunny_talwar]

Script:

Table1:

LOAD *,

  If(CumFlag = 12,

  RangeSum(ActualValue,

  If(Company = Peek('Company'), Alt(Peek('ActualValue'), 0), 0),

  If(Company = Peek('Company', -2), Alt(Peek('ActualValue', -2), 0), 0),

  If(Company = Peek('Company', -3), Alt(Peek('ActualValue', -3), 0), 0),

  If(Company = Peek('Company', -4), Alt(Peek('ActualValue', -4), 0), 0),

  If(Company = Peek('Company', -5), Alt(Peek('ActualValue', -5), 0), 0),

  If(Company = Peek('Company', -6), Alt(Peek('ActualValue', -6), 0), 0),

  If(Company = Peek('Company', -7), Alt(Peek('ActualValue', -7), 0), 0),

  If(Company = Peek('Company', -8), Alt(Peek('ActualValue', -8), 0), 0),

  If(Company = Peek('Company', -9), Alt(Peek('ActualValue', -9), 0), 0),

  If(Company = Peek('Company', -10), Alt(Peek('ActualValue', -10), 0), 0),

  If(Company = Peek('Company', -11), Alt(Peek('ActualValue', -11), 0), 0)), Dual('No 12 months data', 0)) as [Previous 12 Actual Value Total];

LOAD *,

  RangeSum(Flag,

  If(Company = Peek('Company'), Alt(Peek('Flag'), 0), 0),

  If(Company = Peek('Company', -2), Alt(Peek('Flag', -2), 0), 0),

  If(Company = Peek('Company', -3), Alt(Peek('Flag', -3), 0), 0),

  If(Company = Peek('Company', -4), Alt(Peek('Flag', -4), 0), 0),

  If(Company = Peek('Company', -5), Alt(Peek('Flag', -5), 0), 0),

  If(Company = Peek('Company', -6), Alt(Peek('Flag', -6), 0), 0),

  If(Company = Peek('Company', -7), Alt(Peek('Flag', -7), 0), 0),

  If(Company = Peek('Company', -8), Alt(Peek('Flag', -8), 0), 0),

  If(Company = Peek('Company', -9), Alt(Peek('Flag', -9), 0), 0),

  If(Company = Peek('Company', -10), Alt(Peek('Flag', -10), 0), 0),

  If(Company = Peek('Company', -11), Alt(Peek('Flag', -11), 0), 0)) as CumFlag;

LOAD Company,

    MonthYear,

    ActualValue,

    If(ActualValue > 0, 1, 0) as Flag

FROM

Community_171878.xlsx

(ooxml, embedded labels, table is Sheet1);

Output:

Also attaching the qvw document for reference.

HTH

Best,

Sunny