topic Re: Straight table data filtering in QlikView

Straight table data filtering

Wed, 05 Dec 2012 16:25:32 GMT

I am making a P&L table but I only need to show data for the past 3 yrs which means 3 rows of data.

Problem is that the database contains data for more years and for different dates, i.e. there are entries for each quarter.

I need to show the data for 31/12/YYYY for the last three years.

Is it possible to do this in straight table?

Re: Straight table data filtering

Wed, 05 Dec 2012 17:04:03 GMT

for example by set analysis

=sum({$<Date={'31/12/2010'}>}value)

=sum({$<Date={'31/12/2011'}>}value)

=sum({$<Date={'31/12/2012'}>}value)

you need to be more specific and write more info about the result you want to achieve

Re: Straight table data filtering

MayilVahanan — Wed, 05 Dec 2012 17:49:28 GMT

Try like this

=Sum({<Date={'$(=AddYears(YearEnd(max(Date)),-3))','$(=AddYears(YearEnd(max(Date)),-2))','$(=AddYears(YearEnd(max(Date)),-1))'}>}Sales)

for all last three years data

=Sum({<Date={">=$(=AddYears(YearEnd(max(Date)),-3))<=$(=AddYears(YearEnd(max(Date)),-1))"}>}Sales)

Hope it helps

Re: Straight table data filtering

Mon, 10 Dec 2012 14:42:38 GMT

Where would I put this? Why would I need to use Sum? Sorry for excessive questions but I'm a newbie, any help is appreciated, thanks

I have fields like:

total_revenue, total_costs, tax, date

date field is the one that i want to filter by

Re: Straight table data filtering

MayilVahanan — Mon, 10 Dec 2012 17:59:45 GMT

if you want to sum all the total revenue for three year end date, you can use like this

=Sum({<Date={'$(=AddYears(YearEnd(max(Date)),-3))','$(=AddYears(YearEnd(max(Date)),-2))','$(=AddYears(YearEnd(max(Date)),-1))'}>}total_revenue)

for all last three years data

=Sum({<Date={">=$(=AddYears(YearEnd(max(Date)),-3))<=$(=AddYears(YearEnd(max(Date)),-1))"}>}total_revenue)

Re: Straight table data filtering

Tue, 11 Dec 2012 10:09:31 GMT

I dont need to do any calculations, I have all the values in the database. All I need to do is only show the values where date is 31/12/YYYY.

Last 3 years would be a bonus. I tried using your statement but it said error in calculation. Of course I replaced the field names with database ones.

I tried using it in a different table and no data is being diplayed, just the table title.

Re: Straight table data filtering

Thu, 13 Dec 2012 11:16:50 GMT

All I need is to display it for the past 3 yrs (2011, 2010, 2009 in this case) for 31/12/YYYY (31/12/2011). In this case I would have 31/12/2011, 31/12/2010 and 31/12/2009 left only.

Hope this makes sense and it will be easier to understand what I would like to do

Re: Straight table data filtering

MayilVahanan — Thu, 13 Dec 2012 21:01:38 GMT

Try like this

in set analysis

Sum({<Date = {"=Date(floor(YearEnd(Date))) = Date"},Year={'>=$(=Max(Year)-3)<=$(=Max(Year)-1)'}>}total_revenue)

Have a doubt?

=Sum({<Date={'$(=AddYears(YearEnd(max(Date)),-3))','$(=AddYears(YearEnd(max(Date)),-2))','$(=AddYears(YearEnd(max(Date)),-1))'}>}total_revenue)

in this, you can't get the result? can you post a sample?

Hope it helps

Re: Straight table data filtering

Fri, 14 Dec 2012 10:38:23 GMT

I do not need to sum anything so thats what i would have in my case:

={<statement_Date={'$(=AddYears(YearEnd(max(statement_Date)),-3))','$(=AddYears(YearEnd(max(statement_Date)),-2))','$(=AddYears(YearEnd(max(statement_Date)),-1))'}>}PROFIT_LOSS_BEFORE_TAX-TAX_RECLAIMED

PROFIT AFTER TAX (PROFIT_LOSS_BEFORE_TAX-TAX_RECLAIMED) is the only expression in the table, others are dimensions.

Date field is called statement_date in the database

How would your set analysis statement have to be adapted to match this?

Re: Straight table data filtering

Fri, 14 Dec 2012 12:01:54 GMT

So this statement works which means one part of my task is completed:

if(Day(STATEMENT_DATE) = 31 AND Month(STATEMENT_DATE) = 12, date(STATEMENT_DATE))

This only leaves the data values for dates ending in 31/12/YYYY. Now I just need to figure out how to limit it to the last 3 years.

Limited the values by going on Presentation tab and clicking on Max box and choosing 3. Table displays the data that I wanted.

Thanks for help everyone

Re: Straight table data filtering

MayilVahanan — Fri, 14 Dec 2012 18:00:23 GMT

For that, you can try like this

=If(Match(STATEMENT_DATE,YearEnd(STATEMENT_DATE)),STATEMENT_DATE) as dimension

If(STATEMENT_DATE = YearEnd(STATEMENT_DATE),STATEMENT_DATE) as dimension

Re: Straight table data filtering

maksim_senin — Tue, 05 Feb 2013 23:25:19 GMT

Guys,

I'm not sure whether it helps to your application but the best practice is not to use if() in dimensions or expressions due to big impact to performance. The best practice is to use set analysis and in your case it's possible to create flag in calendar for each december 31th, also you can have such flags for periods you need to analyse (previous year, last 3 year, etc).

Believe me since I managed with performance degradation only after rewriting all if()'s with set analysis expressions in an application with 300 millions transactions.

Best regards,

Maxim