13 Replies Latest reply: Apr 28, 2016 6:25 AM by Sangram Reddy

Year Comparison

Hi There,

I am trying to do this, I have an bar graph with total count of something, distributed by year.

So, I Want to, when I click in 2015 another graph shows me the total count of the 2014 year, when a click in 2016 this graphs shows me the 2015 year, so goes on...

Does anyone knows how to do this?

Rodrigo Peixoto.

• Re: Year Comparison

If you select only one year then you can adapt the expression in the target chart to show the count of the previous year. Suppose that chart now has an expression like count(Orders) you can change that to count({<Year={\$(=max(Year)-1)}>}Orders). This assumes that your year field has the name Year. Make sure to use the correct case sensitive field names of your document.

• Re: Year Comparison

Try this

SUM({\$<Year={\$(=Max(Year)-1)}>} SalesAmount)

• Re: Year Comparison

You should be able to use The As-Of Table conecpt here : The As-Of Table

• Re: Year Comparison

All of this formulas gave me the 0 result.

I am thinking that te as-of table concept will be the only way to do this.

• Re: Year Comparison

The other options will work, but we won't know the exact scenario of what you are doing, would you be able to share a sample to look at and propose a working expression?

• Re: Year Comparison

I've started a new app and make this formulas and worked, but in my first app it can't be done.

In my original app, I've made a join with a kml, can this be the cause of the problem?

• Re: Year Comparison

I've found my problem.

I was using the formula =year(date)  to get the year, but I also have the fiel "Year" in my table. Using the field Year, I could get the information, but by year(date) no. There is a way to do with the formula "=year(date)"?

• Re: Year Comparison

Where were you using Year(Date) in the expression?

• Re: Year Comparison

I have one drill-down graph, where I use the formula =year(date) and =month(date) to make the field.

So, in this drill-down graph, if I choose one year, the other graph with this formula (SUM({\$<Year={\$(=Max(Year)-1)}>} SalesAmount) , nothings happens, but, if I make one bar graph use the field year (instead of getting the year by the formula =year(date)) its works fine.

Anyway to solve this problem?

• Re: Year Comparison

Sunny,

I have solved the problem. I just use the field "Year" and "Month" to create de Drill-down graph instead of use the formula =YEAR(DATE) and =month(DATE) and now it's works perfect.

But, using this formula (SUM({\$<Year={\$(=Max(Year)-1)}>} SalesAmount) it always shows me some value (My highest value is 2016, so when I star to look to the application, the sum from 2015 are always visible), is there a way to make this graph to be showing in blank and only when I choose some year it shows to me the sum?

• Re: Year Comparison

Yes, that should be easy. try this:

If(GetSelectedCount(Year) > 0, Sum({\$<Year={\$(=Max(Year)-1)}>} SalesAmount)

• Re: Year Comparison

Not sure what you mean when you say nothing happens? The expression still shows the same value?

May be try this expression where you ignore the selection in Date field and determine the expression's value based on Year field: Sum({<Year = {\$(=Max(Year) - 1)}, Date>} SalesAmount)

• Re: Year Comparison

Hi Rodrigo,

You can even do this:

in your master calendar create 2 more columns with these expressions:

inyear ([Date you want to analyse], today()) as [Present Year],

inyear ([Date you want to analyse], today(), -1 ) as [Previous Year],

now use these flags in your set analysis:

count({\$< [Present Year] = {1} >}Orders)    //- current year orders

count({\$< [Previous Year] = {1} >}Orders)   //- previous year orders

Thanks,

Sangram.