topic Re: Use Set Analysis to Limit Data in a Chart? in QlikView

Use Set Analysis to Limit Data in a Chart?

Mon, 03 Jun 2013 09:47:39 GMT

Hi I have a MonthYear field Jan-13, Feb-13, Apr-13, May-13 etc etc, and I only need to show the last 6 months of data in a chart?

Is there anyway I can use set analysis within my expression to do this?

Many thanks.

Re: Use Set Analysis to Limit Data in a Chart?

er_mohit — Mon, 03 Jun 2013 09:54:03 GMT

See the file attached

Re: Use Set Analysis to Limit Data in a Chart?

sushil353 — Mon, 03 Jun 2013 09:54:39 GMT

Yes,

Try like this..

here i am assuming date_field having MM/DD/YYYY format.

sum({<date_field={">=date(max(date_field)-6,'MM/DD/YYYY') <max({1}date_field)"}>}value)

HTH

Sushil

Re: Use Set Analysis to Limit Data in a Chart?

saumyashah90 — Mon, 03 Jun 2013 09:57:11 GMT

=(only({1}{< [Month Year]={"Jan-13,Feb-13,Mar-13,etc"}>} [Data Field]))

Re: Use Set Analysis to Limit Data in a Chart?

Mon, 03 Jun 2013 13:15:25 GMT

Hi,

Thanks for all your replies. I have just noticed that in my dataset I have a rolling_12 month flag which is has values 1-12. So, I suppose I need to show all data where rolling_month is < 6. Would the set analysis be easier using this field?

Many thanks again,

Re: Use Set Analysis to Limit Data in a Chart?

Mon, 03 Jun 2013 13:32:47 GMT

Hi, Thanks for your sample. Unfortunately, I am unable to see the properties of the chart. The only way I can open the file is to 'open in server'.

Can you post your expresion please?

Thanks again,

Re: Use Set Analysis to Limit Data in a Chart?

er_mohit — Mon, 03 Jun 2013 18:25:04 GMT

here in script i have two Date Format

date(Date,'MMM-YYYY')AS DATE, //Bold one is my Date field

date(Date,'DD-MM-YYYY')AS Date

in dimension i used this date field-->DATE ..meand MMM-YYYY

in expression

write this

sum({<Date = {">=$(=MonthStart(Max(Date), -6))<=$(=MonthEnd(Max(Date)-1))"},Year=,Month=>}Sale)

Hope it helps