Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
martin_hamilton
Creator
Creator

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
sunny_talwar

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)

View solution in original post

11 Replies
sunny_talwar

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
Partner - Specialist III
Partner - Specialist III

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
Creator
Creator
Author

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
Creator
Creator
Author

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

Thanks

Martin

sunny_talwar

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)

Anonymous
Not applicable

Hi,

Set Analysis Syntax of not equal is : -=

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

martin_hamilton
Creator
Creator
Author

Thanks Sunny

martin_hamilton
Creator
Creator
Author

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

sunny_talwar

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)