Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
martin_hamilton
Contributor

Count by records created before current month

Hi - using sense I wish to use a KPI to show a calculation which counts records created before the current MonthName (where the field name is SUBS_CONNECTED_MONTH)

My expression works fine when selecting dates equal to todays Month Name:

Count({<SUB_CONNECTED_MONTH={"$(vCurrentMonth)"}>}Distinct SUBS_NUMBER)

Where I have a variable of :

vCurrentMonth:

=MonthName(Today(1))

I have tried to use a not equal value of <> instead of = but still cant seem to get it to work?

Any ideas or suggestions?

thanks

Martin

1 Solution

Accepted Solutions

Re: Count by records created before current month

Try these changes...

1) Create SUB_CONNECTED_MONTH like this

Date(MonthStart(Date#(Right(SUBS_CONNECTED_DATE, 6)), 'MMM-YYYY') as SUB_CONNECTED_MONTH

2) Then try this expression

Count({<SUB_CONNECTED_MONTH = {"$(='<' & Date(Max(SUB_CONNECTED_MONTH), 'MMM-YYYY'))"}>}Distinct SUBS_NUMBER)

10 Replies

Re: Count by records created before current month

How do you create SUB_CONNECTED_MONTH in the script? Do you use MonthName function? Also, do you make selections in other date and time related fields?

arvind_patil
Valued Contributor III

Re: Count by records created before current month

Hi Martin ,

Create New Variable:

Settinng-> Variable Overview->Add

Varable name- VpreviousMonth

In Defination:

=AddMonths(MonthName(Today()),-1)



And  use that variable  in your expression:


Count({<SUB_CONNECTED_MONTH={"$(VpreviousMonth)"}>}Distinct SUBS_NUMBER)


Thanks,

Arvind Patil

martin_hamilton
Contributor

Re: Count by records created before current month

Hi Sunny, yes I do. see below script:

SUBSCRIPTION_INVENTORY:

LOAD

    SUBS_NUMBER,

    Date(Date#(Right(SUBS_CONNECTED_DATE, 6), 'YYMMDD')) as SUBS_CONNECTED_DATE,

    MonthName(Date(Date#(Right(SUBS_CONNECTED_DATE, 6), 'YYMMDD'))) as SUB_CONNECTED_MONTH

The original date being passed is from an IBM iseries so is in the format CYYMMDD hence the formatting.

This part of the app would be standalone so I would expect that selections should change the displayed value -= if thats what you mean?

Thanks

Martin

martin_hamilton
Contributor

Re: Count by records created before current month

Hi Arvind, this would work in calculating the previous months totals but I am after all dates not equal to current month.

Thanks

Martin

Re: Count by records created before current month

Try these changes...

1) Create SUB_CONNECTED_MONTH like this

Date(MonthStart(Date#(Right(SUBS_CONNECTED_DATE, 6)), 'MMM-YYYY') as SUB_CONNECTED_MONTH

2) Then try this expression

Count({<SUB_CONNECTED_MONTH = {"$(='<' & Date(Max(SUB_CONNECTED_MONTH), 'MMM-YYYY'))"}>}Distinct SUBS_NUMBER)

kerdoncuff
New Contributor III

Re: Count by records created before current month

Hi,

Set Analysis Syntax of not equal is : -=

=Count( Distinct {<SUB_CONNECTED_MONTH-={"$(vCurrentMonth)"}>} SUBS_NUMBER)

martin_hamilton
Contributor

Re: Count by records created before current month

Thanks Sunny

martin_hamilton
Contributor

Re: Count by records created before current month

Hi Sunny, I missed something off my first question but I thought I had it cracked. i tried to add in another selection criteria but it didnt seem to work?

=COUNT({<[SUBSCRIPTION_STATUS] = {'Connected'}>},SUB_CONNECTED_MONTH = {"$(='<' & Date(Max(SUB_CONNECTED_MONTH), 'MMM-YYYY'))"}>},Distinct SUBS_NUMBER)

This is asking for a date status as well. can you see whats wrong with it? i have tried all kinds of variations.

Thanks

Martin

Re: Count by records created before current month

You have an extra >} after the set analysis for SUBSCRIPTION STATUS... Try this

=Count({<[SUBSCRIPTION_STATUS] = {'Connected'}, SUB_CONNECTED_MONTH = {"$(='<' & Date(Max(SUB_CONNECTED_MONTH), 'MMM-YYYY'))"}>} DISTINCT SUBS_NUMBER)

Community Browser