Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

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

13 Replies
jonathandienst
Partner
Partner

Should the second expression not be:

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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)

table.pngline graph.png


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.



balrajahlawat
Champion
Champion

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

balrajahlawat
Champion
Champion

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

Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

what does:

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


return?

Not applicable
Author

it will return 5430 instead of 5428.