Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am using two different expressions like below:
1. To get the count of last month in KPI. Getting value as 5430.
count({$<MonthPeriod= {"$(=Month(AddMonths(Today(),-1)))"}, DateType= {'invitation'}>} invitation_id)
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.
In 1 I am getting wrong count any one please help on this.
Regards,
Pramod
Should the second expression not be:
Count({$<DateType = {'invitation'}, MonthPeriod = {"<=$(=Max(MonthPeriod))"}>}invitation_id)
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:
count({$<MonthPeriod= {"$(=Month(AddMonths(Today(),-1)))"}, DateType= {'invitation'}>} invitation_id)
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.
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
You want only previous month data or till previous month data??
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.
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.
yes, in the first it's picking wrong count.
what does:
count({$<MonthPeriod= {"<$(=Month(AddMonths(Today(),-1)))"}, DateType= {'invitation'}>} invitation_id)
return?
it will return 5430 instead of 5428.