Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
atafsson
Creator
Creator

Expression for Start end date - SET Analysis

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

5 Replies
CELAMBARASAN
Partner - Champion
Partner - Champion

Island data cannot be linked by using set analysis.

Why cannot you link those 2 tables?

atafsson
Creator
Creator
Author

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
Partner - Champion
Partner - Champion

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
Partner - Champion
Partner - Champion

Attachment would help

atafsson
Creator
Creator
Author

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?