13 Replies Latest reply: Dec 15, 2015 8:12 AM by Kumar Pramod

# Difference in last month value

Hi all,

I am using two different expressions like below:

1. To get the count of last month in KPI. Getting value as 5430.

2. This measure in line graph which shows the values of months. in this graph for the last month value it's showing value as 5428.

Count({\$<DateType = {'invitation'},MonthPeriod = {"<\$(=Max(MonthPeriod))"}>}invitation_id)

As i am showing the value of same month it should be same, but it is mismatch.

Correct count/value is = 5428.

Finding difficulty to find where i am doing wrong.

Monthperiod is a field created using load script.

Month(weekend(CanonicalDate)) as MonthPeriod

I tried with Max(MonthPeriod)-1 it's not working because monthperiod is like 'Dec' but this gives output as 11.

Regards,

Pramod

• ###### Re: Difference in last month value

Should the second expression not be:

Count({\$<DateType = {'invitation'}, MonthPeriod = {"<=\$(=Max(MonthPeriod))"}>}invitation_id)

• ###### Re: Difference in last month value

Present month is Dec i am not needed dec i need till Nov so i have used

Count({\$<DateType = {'invitation'},MonthPeriod = {"<\$(=Max(MonthPeriod))"}>}invitation_id)

I am fine with second equation and also the counts of second equation.

I have issue with the 1st equation which is used in the table:

In the above pictures you can see the difference for Nov-15 month the correct count is 5428 but in table i'm getting 5430.

• ###### Re: Difference in last month value

You want only previous month data or till previous month data??

• ###### Re: Difference in last month value

It would give you 11 becoz max() returns integer only.

Why not create a monthnumber field at script in calender table like this:

Year*12+month(Date) as MonthNumber

and then try this expression:

=Count({\$<DateType = {'invitation'}, MonthNumber = {"\$(=Max(MonthNumber))"}>}invitation_id)   //for max month

=Count({\$<DateType = {'invitation'}, MonthNumber = {"\$(=Max(MonthNumber)-1)"}>}invitation_id)   //for previousmonth

• ###### Re: Difference in last month value

My Month period is different it is not for the Monthstart to month end,

it is  "Month(weekend(CanonicalDate)) as MonthPeriod"

If i take MonthNumber it will give the monthstart to monthend count.

I need both previous month data and till previous month data.

previous month in table.

till previous month data in line graph.

• ###### Re: Difference in last month value

the two formulas aren't the same.

In the first, you ask for MonthPeriod = Previous Month

in second, you ask for MonthPeriod < Current Month

Not sure why first would give you a higher result, but a start point would be to make them both the same.

• ###### Re: Difference in last month value

yes, in the first it's picking wrong count.

• ###### Re: Difference in last month value

what does:

return?

• ###### Re: Difference in last month value

it will return 5430 instead of 5428.

• ###### Re: Difference in last month value

returns the same as:

count({\$<MonthPeriod= {"\$(=Month(AddMonths(Today(),-1)))"}, DateType= {'invitation'}>} invitation_id) ?

one should return a count of invitation_id up to and including the Previous Month

the other should return a count of invitation_id for the Previous Month

• ###### Re: Difference in last month value

Yes, but this is returning wrong count

This will return 5430 as count of invitation for nov month

But 1st expression return 5428 for Nov month

• ###### Re: Difference in last month value

1. count({\$<MonthPeriod= {"\$(=Month(AddMonths(Today(),-1)))"}, DateType= {'invitation'}>} invitation_id)

2. Count({\$<DateType = {'invitation'},MonthPeriod = {"<\$(=Max(MonthPeriod))"}>}invitation_id)

1. Says MonthPeriod = November

2. Says MonthPeriod < December

"= November" is not the same as "< December" as October is also < December

• ###### Re: Difference in last month value

That's fine you can see in above image that  "< December" is used for line graph.

then the month value should be same right.

I have cross checked in DB the value is 5428, i don't know from where 2 is adding for Nov month