topic Avoid Data Island in QlikView

Avoid Data Island

parpatra — Tue, 09 Nov 2010 19:30:39 GMT

Hi,

I have a huge data set (> 2million) and have a requirement to show sum(Amount) in current month divided by sum(Amount) in all previous month except current.

I was able to implement this using an unconnected date table and used expression like sum(amount) where month=currmonth and sum(amount) where month<current month.

Now since the data set is huge, I want to avoid use of an unconnected table.

Kindly suggest me some approach.

Thanks in advance!!

Avoid Data Island

Miguel_Angel_Baeyens — Tue, 09 Nov 2010 19:46:04 GMT

Hi,

Give a try to this document. I think the variables approach would work for you.

Hope that helps

Avoid Data Island

johnw — Tue, 09 Nov 2010 20:02:46 GMT

By current month, do you mean the current month and not the selected month, or just one of many months in a chart? If really the curent month, set analysis would probably do the trick. The exact syntax will depend on how your fields are defined, but perhaps something like this:

sum({<MonthYear={ '$(=date(monthstart(today()),'MMM YYYY'))'}>} Amount)
/sum({<MonthYear={"<$(=date(monthstart(today()),'MMM YYYY'))"}>} Amount)

Avoid Data Island

parpatra — Tue, 09 Nov 2010 20:09:15 GMT

Thanks Miguel,

I need some clarifications.

In the example you have created a master calender which can be compared to my date table (only that it is disconnected) , Now I will join it to my source table on the date field. Then create 2 varibales as start date and end date. Then on the Bar chart use Month(Date) from the date table as dimension.

I am confused with the expression to be used. Please help.

Thanks

Avoid Data Island

parpatra — Tue, 09 Nov 2010 20:13:25 GMT

Thanks John,

I want to show the result for all months within a year (Jan2009 till Dec2009). In such a cas probably set analysis wont work as we cannot create diff sets within a particular selection.

Please advice as how this can be resolved.

Thanks

Avoid Data Island

johnw — Tue, 09 Nov 2010 20:23:39 GMT

In that case, I'd generate an AsOf table.

AsOfMonth, MonthType, Month
Jan 2009, Current, Jan 2009
Feb 2009, AllPrevious, Jan 2009
Feb 2009, Current, Feb 2009
Mar 2009, AllPrevious, Jan 2009
Mar 2009, AllPrevious, Feb 2009
Mar 2009, Current, Mar 2009
etc.

dimension = AsOfMonth
expression = sum({<MonthType={'Current'}>} Amount)
/sum({<MonthType={'AllPrevious'}>} Amount)

Avoid Data Island

parpatra — Tue, 09 Nov 2010 20:37:21 GMT

Thanks John,

I have seen similar posts too where you have sugegsted to use a 'asof table'. But my question is how do we handle this when we move from 2009 to 2010 and still wish to go back and view the 2009 data.

Also is there a way I can handle this by changing my model?

Thanks

Avoid Data Island

johnw — Tue, 09 Nov 2010 20:43:34 GMT

Do you mean that the denominator for Mar 2010 should include Jan 2009 through Feb 2010? Just continue the table forward:

AsOfMonth, MonthType, Month
Mar 2010, AllPrevious, Jan 2009
Mar 2010, AllPrevious, Feb 2009
...
Mar 2010, AllPrevious, Dec 2009
Mar 2010, AllPrevious, Jan 2010
Mar 2010, AllPrevious, Feb 2010
Mar 2010, Current, Mar 2010
...

There's nothing magic going on. Just connect the AsOfMonths to the Months as your requirements dictate. For instance, if you meant instead that you did NOT want to include 2009 for Mar 2010, then just don't include 2009 for Mar 2010 when you're generating the table.

Avoid Data Island

parpatra — Tue, 09 Nov 2010 20:50:28 GMT

Thanks John,

I need to include 2009 for 2010 ie. I need to go back to all prior months. And in my case we have data worth 10 years. So I need to go back till 2000 for showing result for any month in 2009.

In such a situation creating an Asof table will be too complex.

Avoid Data Island

johnw — Tue, 09 Nov 2010 21:01:47 GMT

Too complex? In what sense? The script is short, it will execute quickly, and it will take very little memory when done.

AsOf:
LOAD DISTINCT Month
RESIDENT YourCalendarTable // assuming you have one
;
LEFT JOIN (AsOf)
LOAD Month as AsOfMonth
RESIDENT AsOf
;
INNER JOIN (AsOf)
LOAD *
,if(AsOfMonth=Month,'Current','AllPrevious') as MonthType
RESIDENT AsOf
WHERE AsOfMonth>=Month
;

Avoid Data Island

Wed, 15 Dec 2010 19:18:29 GMT

John,

Been browsing the Community for a better understanding of Islands and stumbled on your post.

Very simple very elegant.

I just used it and I wanted to say thanks.

Rich De Rocco