Count by records created before current month

[martin_hamilton]

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

[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]

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]

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]

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 · Accepted solution]

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]

Hi,

Set Analysis Syntax of not equal is : -=

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

[martin_hamilton]

Thanks Sunny

[martin_hamilton]

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)