topic Expand selection in set analysis in QlikView

Expand selection in set analysis

Tue, 05 Apr 2011 12:16:58 GMT

Hi,

I'm trying to have a chart that displays the sales for the selected and the five prior months.

I have created two varibles that selects the first month I want to display, for example, if I select 5 the varible holds the number 12. The second varible holds the year for the first month I want to display.

The problem is that I dont know how to tell QV to select the dates in between the selected month and the month that the varible is holding.

Example: If I select Year 2011 and month 5 I want the chart to look like this:

Year Month Sales

2010 12 10

2011 1 15

2011 2 25

2011 3 30

2011 4 10

2011 5 50

I have included a sample file.

Maybe I'm making this a bit complicated for myself? 🙂

Expand selection in set analysis

stephencredmond — Tue, 05 Apr 2011 13:42:25 GMT

My suggestion is for you to add a new field to hold the date of the month (I used MakeDate(Year, Month) to create a date representing the 1st of the month). Use this field in your set and then you can manipulate it easier using AddMonths.


=Sum ({$<Month=,Year=,MonthDate={">$(=AddMonths(max(MonthDate),-6))<=$(=max(MonthDate))"}>}Sales)

Regards,

Stephen

Expand selection in set analysis

Tue, 05 Apr 2011 14:41:55 GMT

Thank you so much for your fast reply!

I will try this first thing thursday when I get back to work.

Again, thanks alot!

Expand selection in set analysis

Thu, 07 Apr 2011 13:13:03 GMT

It works great, thanks alot!