Qlik Sense- Count from previous month

[Anonymous]

Hey guys,

I want a KPI with the number of records from the previous month.

So I'm trying to do the following:

=count({$<Date_Year={'$(=year(vMaxData))'}, Date_Month={"=$(=addmonths(month(vMaxData),-1))"}>} Canal)

vMaxData is a variable I created as max date. And it is working because I use it in other calculations.

this way is not giving any error but the counting is not right.

Can you help me?

Best Regards,

Miguel Cunha

[YoussefBelloum]

Hi,

supposing that your Date_Month field is a MONTH FIELD (example 1, 2, 3...12), try this:

=count({$<Date_Year={'$(=year(vMaxData))'}, Date_Month={"=$(=num(Month(addmonths(vMaxData,-1))))"}>} Canal)

if your Date_Month field is a date field, try this:

=count({$<Date_Year={'$(=year(vMaxData))'}, Date_Month={"=$(=addmonths(vMaxData,-1))"}>} Canal)

if none of these expressions works, try to share with us your Date_Month format

[Anonymous]

Hi youssefbelloum‌

None of them worked.

My Date_Month is Month(DataImport) As Date_Month.

The DataImport format is DD-MM-YYYY.

[YoussefBelloum]

what do you have exactly on your vMaxData variable ?

[Anonymous]

=Max(Date_Date)

[YoussefBelloum]

ok, so the problem on your original expression is this:

addmonths() function takes a date to roll back but you give it a Month

Date_Month field is in this format: 01 02 ...12 or this format 1 2 3 12 ?

[Anonymous]

it's in the 01, 02 format

[sunny_talwar]

You have an extra = sign... can you see if it works after you remove it?

=count({$<Date_Year={'$(=year(vMaxData))'}, Date_Month={"=$(=addmonths(month(vMaxData),-1))"}>} Canal)

[sunny_talwar · Accepted solution]

Also, may be you need Month on top of AddMonths

=Count({$<Date_Year = {'$(=Year(vMaxData))'}, Date_Month = {"$(=Month(AddMonths(vMaxData, -1)))"}>} Canal)

[YoussefBelloum]

try this:

=count({$<Date_Year={'$(=year(vMaxData))'}, Date_Month={"=$(=num(Month(addmonths(vMaxData,-1)),00))"}>} Canal)