Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
berryandcherry6
Creator II
Creator II

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 .


Please help me in this regard.Any kind of help is appreciated.


Regards.

21 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
arulsettu
Master III
Master III

remove year from dimension and check

berryandcherry6
Creator II
Creator II
Author

Hi,

Thanks for reply

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.

arulsettu
Master III
Master III

check this image reason for getting negative values

Untitled.png

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)

)

berryandcherry6
Creator II
Creator II
Author

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.

berryandcherry6
Creator II
Creator II
Author

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.

Please help me on this

arulsettu
Master III
Master III

what is it?

berryandcherry6
Creator II
Creator II
Author

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.

arulsettu
Master III
Master III

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