Expression for Start end date - SET Analysis

[atafsson]

Hi all!

I need help to create a SET-analysis expression instead of my if-statement.

I have this expression:

if( Startdate <= date(Month,'YYYYMM') xor date(Month,'YYYYMM') >= Enddate,sum(Amount))

The expression gives the correct result but it takes very long time to calculate it for Qlikview. I have around 40 thousands of ID's with startdates from 2014-01-01 to 2039-12-31 it gives me many rows to generate the dates in the script. Every ServiceId has a uniqe start and enddate. I've also created i report structure so the serviceId's occurs on multiple places.

My Calendar is a data island.

What I would like to do is to create i SET-expression of my expression instead of the if-statement.

I attached a sample-file with a example (not for the report structure).

/AG

[CELAMBARASAN]

Island data cannot be linked by using set analysis.

Why cannot you link those 2 tables?

[atafsson]

Hi,

I'd like to, but I don't know how to create the link between them because of the start and enddate.

How should the the link to the calendar table take both start- and end-date into account?

How will I generate the dates in the chart between start- and end-date?

Regards

Axel

[CELAMBARASAN]

I changed this part of script

Calendar:

LOAD

  Year(addmonths($(vMin),IterNo()-1)) as %Year

  ,MonthStart(addmonths($(vMin),IterNo()-1)) as Month

  ,Year(addmonths($(vMin),IterNo()-1)) & num(month(MonthStart(addmonths($(vMin),IterNo()-1))),'00') as YearMonth

  ,Date($(vMin)+(Iterno()-1),'YYYY-MM-DD') as Date

  ,month(MonthStart(addmonths($(vMin),IterNo()-1))) as Month2

AutoGenerate(1)

//while addmonths($(vMin),IterNo()-1) <= $(vMax)  ;

While Date($(vMin)+(Iterno()-1)) <=Date($(vMax));

drop table Temp;

LinkTable:

LOAD

  ServiceId,

  Date((Startdate + IterNo() - 1), 'YYYY-MM-DD') AS Date

Resident Tmp

While (Startdate + IterNo() - 1) <= Enddate;

And Use Expression as simply Sum(Amount)

Regards,

Celambarasan

Edit:

There was a issue calendar generation, use the below script

Calendar:

LOAD

  Date,

  Year(Date) as %Year

  ,MonthStart(Date) as Month

  ,Num#(Text(Date(Date, 'YYYYMM'))) as YearMonth

  ,month(MonthStart(Date)) as Month2;

LOAD

  Date($(vMin)+(Iterno()-1),'YYYY-MM-DD') as Date

AutoGenerate(1)

//while addmonths($(vMin),IterNo()-1) <= $(vMax)  ;

While Date($(vMin)+(Iterno()-1)) <=Date($(vMax));

drop table Temp;

LinkTable:

LOAD

  ServiceId,

  Date((Startdate + IterNo() - 1), 'YYYY-MM-DD') AS Date

Resident Tmp

While (Startdate + IterNo() - 1) <= Enddate;

Regards,

Celambarasan

[CELAMBARASAN]

Attachment would help

[atafsson]

The problem is that with the LinkTable the script needs to generate all dates from start to end for each ServiceId.

In my real application it counted up to around 200 million rows in the LinkTable.

Is there an other way?