Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Tags (1)
13 Replies
MVP
MVP

Re: Difference in last month value

Should the second expression not be:

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

Not applicable

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:

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
Esteemed Contributor

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

balrajahlawat
Esteemed Contributor

Re: Difference in last month value

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

Not applicable

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.

Not applicable

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.

Not applicable

Re: Difference in last month value

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

Not applicable

Re: Difference in last month value

what does:

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


return?

Not applicable

Re: Difference in last month value

it will return 5430 instead of 5428.

Community Browser