Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
parpatra
Partner - Contributor III
Partner - Contributor III

Avoid Data Island

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!!

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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
;

View solution in original post

10 Replies
Miguel_Angel_Baeyens

Hi,

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

Hope that helps

johnw
Champion III
Champion III

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)

parpatra
Partner - Contributor III
Partner - Contributor III
Author

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

parpatra
Partner - Contributor III
Partner - Contributor III
Author

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

johnw
Champion III
Champion III

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)

parpatra
Partner - Contributor III
Partner - Contributor III
Author

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

johnw
Champion III
Champion III

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.

parpatra
Partner - Contributor III
Partner - Contributor III
Author

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.

johnw
Champion III
Champion III

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
;