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

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

jblomqvist — Thu, 09 Jul 2015 11:19:39 GMT

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?

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

sunny_talwar — Thu, 09 Jul 2015 11:34:16 GMT

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

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

maxgro — Thu, 09 Jul 2015 11:56:52 GMT

PFA

// test data

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

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;

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;

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

jblomqvist — Thu, 09 Jul 2015 12:25:58 GMT

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

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

jblomqvist — Thu, 09 Jul 2015 12:32:54 GMT

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

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

sunny_talwar — Thu, 09 Jul 2015 12:53:00 GMT

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