topic Rolling 12 Month Sales using Set Analysis Chart Sort? in QlikView

Rolling 12 Month Sales using Set Analysis Chart Sort?

Anonymous — Wed, 25 May 2011 11:08:19 GMT

Hello

I am struggling with the following problem, i have a chart that need to compare over months this years sales and the previous years sales however the graph needs to be dynamic in that it changes in accordance with the users selections, the following is my current expression;

Current Sales: sum({1<OrderDate={">=$(=addmonths(monthend(max(OrderDate))+1,-12)) <=$(=max(OrderDate))"}>}Sales)

Previous Sales: sum({1<OrderDate={">=$(=addmonths(monthend(max(OrderDate),)+1,-24)) <=$(=addmonths(max(OrderDate),-12))"}>}Sales)

% difference: (Column(1)-Column(2))/Column(1)

This works fine when a year is selected however when i select a month for example if the Fiscal Year 08/09 is selected as above and i click the month of May what i would like to see is Jun 08 to May 09 (Current Sales) and Jun 07 to May 08 (Previous Sales), this works with regards the dates but the order of the Month in the chart does not relate properly.

Any help would be greatly appreciated.

Message was edited by: shay.cooper I have managed to sort this with the help from colleagues at work, basically it involves using the same expression used for the current sales and replacing sales with MonthYear and adding max to the start rather than sum and works like a charm.

Message was edited by: shay.cooper Added an example of the graph with sample data please excuse percentage change as the data was created using rand function so make no sense really

Rolling 12 Month Sales using Set Analysis Chart Sort?

Anonymous — Thu, 26 May 2011 16:26:19 GMT

I have managed to sort this with the help from colleagues at work, basically it involves using the same expression used for the current sales and replacing sales with MonthYear and adding max to the start rather than sum and works like a charm.

Rolling 12 Month Sales using Set Analysis Chart Sort?

skaredovs — Mon, 13 Jun 2011 13:04:45 GMT

Hi!

Could you please attach the example of the solution. Sounds good.

BR,

Konstantins

Rolling 12 Month Sales using Set Analysis Chart Sort?

Mon, 20 Jun 2011 09:07:40 GMT

@Shay.Cooper

Could you paste the solution, sounds really interesting.

Thanks in advance.

Rolling 12 Month Sales using Set Analysis Chart Sort?

Fri, 01 Jul 2011 01:30:10 GMT

Hi,

Can you share the solutions? I also desperatly needed this..

Thank you

Rolling 12 Month Sales using Set Analysis Chart Sort?

Fri, 01 Jul 2011 06:22:01 GMT

I think more easy is to add a drill down for Year & Month ... may b this is useful for all

Rolling 12 Month Sales using Set Analysis Chart Sort?

Mon, 06 Feb 2012 23:33:49 GMT

I'm trying use the current sales expression, and its not working for me, any ideas why? It is still summing about both current year and last year...

sum({$<"DateType"={'Rolling 28 Days'},"As Of Date"={">=$(=addmonths(monthend(max("As OfDate"))+1,-12)) <=$(=max("As Of Date"))"}>}Cnt)

Rolling 12 Month Sales using Set Analysis Chart Sort?

Anonymous — Thu, 23 Feb 2012 14:41:15 GMT

Hello AlexYour As Of Date is written as "As OfDate" and also "As Of Date" hence the field names dont match and it wont work.I have alos found a more performant function to do this using variables and the inyeartodate function this allows the user to make selections on a date and the function will only pull through the latest year to date info, I will try and quickly write a solution and add up on here when I get the time.

Re: Rolling 12 Month Sales using Set Analysis Chart Sort?

Fri, 09 Mar 2012 17:12:20 GMT

Hi Shay,

I"m looking into this again. Thanks for pointing out my error. It's working now.

Is there a way to make this date specific? I've attached what the want in the Powperpoint file.

I'm using this as my dimension. date([As of Date], 'MM/DD').

Re: Rolling 12 Month Sales using Set Analysis Chart Sort?

Wed, 25 Apr 2012 16:00:18 GMT

Shay, I how did you get your graph to show July then the rest of the months?

This is my calculated dimension. I would like to start with my max "as of date", then show the last 12 months.

=date(makedate(1+(month("As of Date")>7),month("As of Date"),day("As of Date")),'MM/DD')

Re: Rolling 12 Month Sales using Set Analysis Chart Sort?

Anonymous — Wed, 25 Apr 2012 16:11:29 GMT

Hi Alex I am currently downloading the app you sent (Slow connection lol) I will take a look, for now you should be able to put your expression for calculating the first date into the sort by expression tab in the properties menu, in my case I changed the sum function for max and it worked like a charm. If that doesn't work you can do a cheeky cheat and load in your created dates with rowno() you can then sort by rowno(),this must be done in the script.

Re: Rolling 12 Month Sales using Set Analysis Chart Sort?

Wed, 25 Apr 2012 16:46:49 GMT

Man, I thought i had tried the max(As of Date) before...

Anyways, just tried that and it worked perfectly!