topic Filter question in QlikView

Filter question

Wed, 07 Sep 2011 00:27:37 GMT

Hi All,

I created an app and is working fine. In the app, I want to add a table chart that shows sales this year in one column and sales prior year in second column. But here is the problem- I have a list box with all the years and if I select a particular year as filter, it gets applied to this chart as well screwing it up. Any idea how to deal with htis situation?

Thanks in advance!

Filter question

lironbaram — Wed, 07 Sep 2011 05:35:38 GMT

in the expression in the chart use set analysis

something like this

sum({<YEAR={">=$(=max(YEAR)-1)"}>} Sales)

hope its helps you

Filter question

Wed, 07 Sep 2011 14:12:21 GMT

The values change when I click on a particular month. Since this is a YTD calculation, I don't want it to change.

Question - HOw do you guys deal with this situation. Let's say you have a several charts in the report and one table chart that shows sales ytd current yera and sales ytd previous year in two separate columns. Should we have the values not change when user clicks on a year/month or should we have the values in this chart static?

Please advice.

Filter question

Wed, 07 Sep 2011 14:31:50 GMT

one possibility is to create a new table calendar with this columns

Actual year , month, Previous Year, Mark actual, Date (this is the field to be linked with the fact table)

2011 01 2010 S 2011-01

2011 01 2010 N 2010-01

2011 02 2010 S 2011-02

2011 02 2010 N 2010-02

The user have to select the field Actual Year and Actual Month

In the chart it is very important the usage of the MARK ACTUAL by using IFs.

In case you need diferent combination of dates you can create more field and more marks.

I hope you could sove your problem.

Besides this table is very small and the performance of the application is not going to be worst.

Regards,

Marcelo

Filter question

Wed, 07 Sep 2011 15:14:52 GMT

Is there a way to modify the folrmula SUM({<YEAR={">=$(=max(YEAR)-1)"}>} Sales) so that it doesn't affect when users click on a month?

Filter question

SunilChauhan — Wed, 07 Sep 2011 15:20:08 GMT

use this formula

SUM({1<YEAR={">=$(=max(YEAR)-1)"}>} Sales)

Re: Filter question

Wed, 07 Sep 2011 15:26:31 GMT

I don't understand well what happens in your case when user clicks on a month, but usually this approach (as below) helps to ignore selection in a certain field (here - Month).

SUM({< Month=, YEAR={">=$(=max(YEAR)-1)"}>} Sales)

UPDATE

Sunil Chauhan correctly suggests to use {1} for calculation of a previous period, not included into current selection. So final formula should be

SUM({1 < Month=, YEAR={">=$(=max(YEAR)-1)"}>} Sales)

Filter question

Wed, 07 Sep 2011 15:32:07 GMT

you'll need to also put your Month field in that set analysis or it will still be effect it

SUM({1<YEAR={">=$(=max(YEAR)-1)"},[your month field]= >} Sales)

so now it will limit to your year and will also ignore your month selections

hope that helps

Joe

Filter question

SunilChauhan — Wed, 07 Sep 2011 15:36:59 GMT

SUM({1<YEAR={">=$(=max(YEAR)-1)"}>} Sales)

no otheer extra code is required

Filter question

Wed, 07 Sep 2011 15:44:54 GMT

oh that's interesting so the '1' there is basically a catch all for disregarding everything else apart from the Year field? good to know cheers

Joe

Filter question

Wed, 07 Sep 2011 17:19:00 GMT

Hi,

For YTD and LYTD we Below Expression

Current Year : Sum({<Year={$(=Max(Year))}>} Sales)

Previous Year : Sum({<Year={$(=Max(Year)-1)},Month={">$(=Min({<Year={$(=Max(Year))}>} Month-1))<$(=Max({<Year={$(=Max(Year))}>} Month+1))"}>} Sales)

If the data is at Day level then we add another condition in the Previous Year Expression.

Hope This will work for you.

Regards,

Srini

Filter question

Oleg_Troyansky — Wed, 07 Sep 2011 17:45:13 GMT

In the initial request, you said that you want to create a column for "prior year sales". Did you actually need a full prior year sum, or "Prior YTD" sum?

For YTD calculations, to avoid complex Set Analysis expression, I usually create flags in the master calendar table. For each date, I set a flag to 1 if it belongs to YTD (Prior YTD, QTD, etc...). then the Set Analysis condition is very simple:

sum({<YTD_Flag={1}>} Sales) - for current YTD

sum({<PYTD_Flag={1}>} Sales) - for Prior YTD

cheers,

Oleg