topic Re: Qlik Sense- Count from previous month in App Development

Qlik Sense- Count from previous month

Anonymous — Wed, 22 Dec 2021 15:42:54 GMT

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

Re: Qlik Sense- Count from previous month

YoussefBelloum — Wed, 18 Apr 2018 12:31:46 GMT

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

Re: Qlik Sense- Count from previous month

Anonymous — Wed, 18 Apr 2018 12:48:34 GMT

Hi youssefbelloum‌

None of them worked.

My Date_Month is Month(DataImport) As Date_Month.

The DataImport format is DD-MM-YYYY.

Re: Qlik Sense- Count from previous month

YoussefBelloum — Wed, 18 Apr 2018 12:54:19 GMT

what do you have exactly on your vMaxData variable ?

Re: Qlik Sense- Count from previous month

Anonymous — Wed, 18 Apr 2018 14:14:00 GMT

=Max(Date_Date)

Re: Qlik Sense- Count from previous month

YoussefBelloum — Wed, 18 Apr 2018 14:29:45 GMT

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 ?

Re: Qlik Sense- Count from previous month

Anonymous — Wed, 18 Apr 2018 14:42:44 GMT

it's in the 01, 02 format

Re: Qlik Sense- Count from previous month

sunny_talwar — Wed, 18 Apr 2018 14:44:18 GMT

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)

Re: Qlik Sense- Count from previous month

sunny_talwar — Wed, 18 Apr 2018 14:45:34 GMT

Also, may be you need Month on top of AddMonths

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

Re: Qlik Sense- Count from previous month

YoussefBelloum — Wed, 18 Apr 2018 14:49:17 GMT

try this:

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

Re: Qlik Sense- Count from previous month

Anonymous — Wed, 18 Apr 2018 14:50:13 GMT

It worked now!

Thank you so much!

Best regards,

Miguel Cunha

Re: Qlik Sense- Count from previous month

YoussefBelloum — Wed, 18 Apr 2018 14:50:53 GMT

Month() function returns a months in an MMM format, but his Date_Month format is MM here..

Re: Qlik Sense- Count from previous month

sunny_talwar — Wed, 18 Apr 2018 14:51:45 GMT

I saw that, but not sure... may be it is dual with string and number value...

Re: Qlik Sense- Count from previous month

YoussefBelloum — Wed, 18 Apr 2018 14:52:55 GMT

you mean Month() is returning dual values ?

Re: Qlik Sense- Count from previous month

YoussefBelloum — Wed, 18 Apr 2018 14:53:53 GMT

EDITED

miguelcunha

just curious about the last one above, just test it and tell me please

no need to do this, I didn't saw the extra = on all the suggestions above...

Re: Qlik Sense- Count from previous month

sunny_talwar — Wed, 18 Apr 2018 14:55:25 GMT

I mean that Date_Month is a field created like this in the script

Month(Date) as Date_Month

Re: Qlik Sense- Count from previous month

Anonymous — Wed, 18 Apr 2018 14:58:57 GMT

Didn't work mate. It gave me the same result as when I had Date_Month={"=$(=addmonths(month(vMaxData),-1))"}

Re: Qlik Sense- Count from previous month

YoussefBelloum — Wed, 18 Apr 2018 15:01:07 GMT

I see, i was focusing on the output described above (01 02 03..) indeed, Month() return dual values.. but don't know it depends on what it displays a month name or an integer as an output..

Re: Qlik Sense- Count from previous month

sunny_talwar — Wed, 18 Apr 2018 15:03:05 GMT

Month() will display text and Num(Month()) will display number..... unless the environmental variable has been changed to show number also....

Re: Qlik Sense- Count from previous month

YoussefBelloum — Wed, 18 Apr 2018 15:04:50 GMT

so here op's environnement variables is changed ? because when he put it on listbox, it displays 01 02, and when yo use your expression with Month(Addmo... it works..

so what's your thought here ?