21 Replies Latest reply: Aug 11, 2016 7:40 AM by Supriya R

# Showing negative values in Year over year by month

Hi all,

I want to show a line graph where it displays  Year over year by month  of  count of invitations.

I want to show graph for previous and current year.

I have tried with

Dimension : year and Month

Expression :

count({<DateType = {invitation},Year = {\$(=max(Year))}>}invitation_id)-count({<DateType = {invitation},Year ={\$(=max(Year)-1)}>}invitation_id)

I think there is wrong in my expression.So it is showing negative values  I have attached qvf file .

Regards.

• ###### Re: Showing negative values in Year over year by month

In a set analysis expression the set is calculated at the chart level, not the row level. That's why records of one year cannot show data from the previous year using set analysis. See this document for a description of the problem and some solutions:Calculating rolling n-period totals, averages or other aggregations

• ###### Re: Showing negative values in Year over year by month

Hi,

I need to Show counts YOY of completions and Invitation in Line Graph.

I have dimension : Year and Month

Expression:

fabs(count({<DateType = {invitation},

Year = {\$(=max(Year))}>}invitation_id)-count({<DateType = {invitation},

Year = {\$(=max(Year)-1)}>}invitation_id)

)

Here i need to add one more expression, but i am not able to add onemore expression.

Can we add more than one expression while calculating YOY by month??

• ###### Re: Showing negative values in Year over year by month

remove year from dimension and check

• ###### Re: Showing negative values in Year over year by month

Hi,

If i remove year it shows same negative values.

Here should i get counts of invitation of month or years??

Can you say the formula for this to calculate, PLease.

• ###### Re: Showing negative values in Year over year by month

check this image reason for getting negative values

if you want to convert negative to positive

try this

fabs(count({<DateType = {invitation},

Year = {\$(=max(Year))}>}invitation_id)-count({<DateType = {invitation},

Year = {\$(=max(Year)-1)}>}invitation_id)

)

• ###### Re: Showing negative values in Year over year by month

Hi,

Can you check my qvf file,

i have datas for all year and month.You can see that by taking only count of 2015 or 2016.

One more thing, i am using canonical date to store all date_created of invitation,and getting counts of it.

Now i want to get counts of invitation created in this year and last year. So can you check my attached file.

• ###### Re: Showing negative values in Year over year by month

Hi,

with this  below expression

fabs(count({<DateType = {invitation},

Canon_Year = {"\$(=year(max(CanonicalDate)))"}>}invitation_id)-count({<DateType = {invitation},

Canon_Year = {"\$(=year(max(CanonicalDate))-1)"}>}invitation_id))

i am able to get rid of negative values, but i need to add one more expression for graph,how to add one more count.

• ###### Re: Showing negative values in Year over year by month

what is it?

• ###### Re: Showing negative values in Year over year by month

Course Completed Counts

that is :

fabs(count({<DateType = {Completed},

Canon_Year = {"\$(=year(max(CanonicalDate)))"}>}course_completed)-count({<DateType = {Completed},

Canon_Year = {"\$(=year(max(CanonicalDate))-1)"}>}course_completed)).

i want to show counts of YOY by Month of  Invitations and CourseCompleted.

• ###### Re: Showing negative values in Year over year by month

i dont understand you want add this expression with above expression?

• ###### Re: Showing negative values in Year over year by month

Like in below image i need to show graph. For both current(Green) and previous year(red) of  invitations and Course Completed YOY Counts.

• ###### Re: Showing negative values in Year over year by month

this?

• ###### Re: Showing negative values in Year over year by month

Yes.

But it has to show for both current and previous year.

• ###### Re: Showing negative values in Year over year by month

add two expression for current and previous year

cuu yr:

count({<DateType = {Completed},

Canon_Year = {"\$(=year(max(CanonicalDate)))"}>}course_completed)

pre Yr:

count({<DateType = {Completed},

Canon_Year = {"\$(=year(max(CanonicalDate))-1)"}>}course_completed)

• ###### Re: Showing negative values in Year over year by month

I am not able to add one more expression, ADD button of Measures is not Highlighted.Thats the problem i am facing.

• ###### Re: Showing negative values in Year over year by month

in line chart u can add measures as u want. check properly under expression

• ###### Re: Showing negative values in Year over year by month

You wont be able to add Expression more than one

• ###### Re: Showing negative values in Year over year by month

try the option add alternative measure

• ###### Re: Showing negative values in Year over year by month

Nothing get displayed if i add in Alternate measure, in line graph

• ###### Re: Showing negative values in Year over year by month

after adding it click exploration icon

you will see the alternate expression click it

• ###### Re: Showing negative values in Year over year by month

I can see alternate option, but the count datas dont get displayed in line Chart after adding expression in generated report.

This is used to view datas dynamically. If i have generated report it will be static right?