Solved: Year to Date vs Year to date Last Year - Qlik Community

Report Inappropriate Content · ‎2011-02-18

I'm new to Qlikview and am trying to get something I thought was relatively simple but seems to keep alluding me. I've been going through the forums and watched the training on set analysis and didn't have any luck getting the right formula to work. Hopefully you can all help me. 🙂

I'm trying to get a line Chart with YTD numbers, PRevious YTD and Previous MTD numbers. The chart is by day It simply counts by Order_date how many orders there are on each day. I have a date picker for year/month/day that is linked to a mastercalendar. So when someone picks something like 2010-10-15 they should see the YTD up to that point the Previous YTD and the Previous MTD up to that point. When nothing is chosen it can default to showing current day. Which I think it would do by default anyways but perhaps I have to code something for that as well. 🙂

My dates are ORder_date and DetailDay for the master calendar. Any help would be greatly appreciated. Thanks

Pat

stevedark · ‎2011-02-21

Hi Pat,

As you say this is something that is quite a common requirement, yet it is not by any means simple to implement.

I've attached a basic example that should give you an idea of what you need to do to construct what you are looking for.

Perhaps the biggest potential gotcha with these types of comparison (and set analysis on dates in particular) is that the format of the date you are comparing too must exactly match the format of the date in the field. Consistency is king here.

You do not need to put all of the calculations date parts to compare to into variables - but I find it the easiest way to keep tabs on what is happening.

Hope this example helps - post back if you have any questions.

Regards,
Steve

View solution in original post

Report Inappropriate Content · ‎2011-02-21

Pat,

I know there is an easier and better way, but I got it to work by creating an expression for each period to display mtd results for our fiscal year. All I had to do was use this simple formula for each of the expressions. For each month in the fiscal year I adjusted the last argument for the inMonthToDate function. As a result (with some tweaking) I am able to show the number of sales calls as of this date for each month in our fiscal year.

=count(if(inMonthToDate(DATEENTERED, TODAY(),0), NUMBERCALLED))

Kal

stevedark · ‎2011-02-21

Hi Pat,

As you say this is something that is quite a common requirement, yet it is not by any means simple to implement.

I've attached a basic example that should give you an idea of what you need to do to construct what you are looking for.

Perhaps the biggest potential gotcha with these types of comparison (and set analysis on dates in particular) is that the format of the date you are comparing too must exactly match the format of the date in the field. Consistency is king here.

You do not need to put all of the calculations date parts to compare to into variables - but I find it the easiest way to keep tabs on what is happening.

Hope this example helps - post back if you have any questions.

Regards,
Steve

Report Inappropriate Content · ‎2011-02-25

Thank you Steve and Kal for responding my apologies for taking so long to get back. I haven't had a chance to review your Sheet Steve but I'm sure it will help! I was able to get this working after talking to one of the Qlikview tech's I just had some simple issues with the syntax to overcome and was able to fix them. Thanks again!

pat

Report Inappropriate Content · ‎2011-11-01

In the formula, what is the purpose of this set analysis: "Month=,Day=,MonthYear=" ?

Report Inappropriate Content · ‎2011-11-01

mind to explain this set analysis?

=SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,MonthYear=>}Amount)

If i want to see 20 Feb 2011 YTD, the formula show me that it will total amount for:

= Year = 2011

= Date = 20 Feb 2011

= Month = ?

= Day = ?

= MonthYear = ?

for YTD, does it mean total amount from 20 Feb 2010 - 20 Jan 2011?

Do we need to filter the data from 20 Feb 2010 onwards?

stevedark · ‎2011-11-12

This will be YTD up to an including 20 Feb 2011. The two things that are not obvious in the Set Analysis syntax are:

Date={'<=$(vMaxDate)'} This is actually less than or equal to (despite the intial equal sign)

Month= Equal on its own means any value, regardless of selection

Often with set analysis you will have a number of fields that you wish to ignore selections on and have any value, in the case above as the required values are set by the two fields that are defined. I will often have a variable for commonly ignored fields, such as Month, Day and MonthYear above, and the code will read like this:

=SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'}$(vIgnoreFields)>}Amount)

I hope that all makes sense. The other gotcha in Set Analysis syntax is how Not Equals is handled, so for example to ignore totals you may have:

=SUM(<RowType-={'Total'}>}Amount)

Note the easily missed minus sign ahead of the equals symbol.

Hope that helps.

Steve

llauses243 · ‎2011-11-12

Hi Steve,

To your contribution just need ... pls to see image attached

Very thx, Luis

stevedark · ‎2011-11-15

I've uploaded the example that I posted to this thread as a shared QlikView, so it is now available in a Personal Edition friendly format:

http://community.qlik.com/qlikviews/1175

- Steve

Report Inappropriate Content · ‎2013-08-11

Link not working. Please re-upload or advise?

thanks in advance!

BR

Year to Date vs Year to date Last Year

Related Topics