topic Re: Set Analysis for selected dates to freeze in QlikView

Set Analysis for selected dates to freeze

Tue, 11 Aug 2015 14:43:08 GMT

Hello I have a table that I need help with set analysis for.

Sum(Sales) is the sum for each column but I need them to be set at certain date ranges.

The field name is first and is fine as is.

The 2015 column is also fine as is because its a calculation of the first two columns.

When May 2015 is selected I need the table to look like this.

When April 2015 is selected, the first column should change to Jan-Mar 2015, the next April 2015, but the last should stay as Jan-Dec 2014.

If April 2014 was selected. the first column should change to Jan-Mar 2014, the next April 2014, but the last should be Jan-Dec 2013.

Re: Set Analysis for selected dates to freeze

swuehl — Tue, 11 Aug 2015 19:43:59 GMT

You can look into this series of blog posts to learn how you can set time ranges using set analysis:

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

If you understood how the aggregations work, you can use quite similar expressions to create dynamic expression labels. For example, the lable for last column might look like

='Jan - Dec '& max(Year)-1

and the expression like

=Sum({<Year = {$(=max(Year)-1)}, Month=, Quarter=, Date= >} Sales)

Re: Set Analysis for selected dates to freeze

Wed, 12 Aug 2015 12:53:14 GMT

thank you for responding so quickly.

When I use that expression and select May as my month, it only shows May 2014 in that column. I need this to always show full year 2014 regardless of what month I have selected for 2015., do you have advice on that?

Also..

In the first column I have this as my label expression

='Jan - ' & date(if(num(Month)=1,12,num(Month)-1), 'MMM') & ' ' & GetFieldSelections([Year],', ')

which works great unless I have January or February selected. (it wil say Jan-Dec even though it should just be jan then for february it says Jan - Jan where it should just say Jan)

Thank you!