Question Re: CountIf with Dates in Technology Q&A

CountIf with Dates

daniel_schueler — Tue, 28 Feb 2012 15:54:01 GMT

Hi,

I try using the CountIf-function with dates in the conditional parameter of the function and I am experiencing the same problem as stated here: http://forums.sdn.sap.com/thread.jspa?threadID=1756183

In the Designer and in Excel itself everything looks ok, but not in the final swf-file.

I am using the function like this:

=COUNTIF(G$3:G$40; ">"&P4)

while P4 holds the value 40940 (01.02.2012 in numeric format) and G3 to G40 also holds various dates with the cell-style set to "numeric". Does it even have any influence to which format the cell-style is set?

Is there any workaround or am simply doing something wrong?

I'm using Dashboard Design Version 6.0.0.0 Build 14,0,2,364

Thank you,

Daniel

********

Edit:

I changed the source data of the function. P4 now contains the return value of DATEVALUE("01.02.2012"). G3 to G40 also contain now =DATEVALUE(<anotherCell>) while <anotherCell> contains the date in textform.

The result is still the same (right result in Excel, wrong result in the Preview/SWF-file). This is confusing because CountIf should work with numbers, right?

*********

Edit2:

I got around the problem by not using dates at all and use mere text for the dates. I then convert it with some text-functions and VALUE() to numbers and do COUNTIF() on these numbers. Not nice but it works.

Seems like Dashboard Design doesn't like DATEVALUE() even though it is in the list of supported functions.

Edited by: Daniel Schueler on Feb 29, 2012 1:31 PM

Re: CountIf with Dates

hai_murali_here — Thu, 01 Mar 2012 04:34:57 GMT

Hi Daniel,

Yes,you are absolutely right.

Xcelsius(Dashboard) doesnt support some of the Excel functions like Ifsum,countif,counifs,substring,sumif, etc.

The workaround what you did is the only solution.

Rgds,

Murali

Re: CountIf with Dates

Former Member — Mon, 05 Mar 2012 11:12:00 GMT

Hi Murali,

I have used Countif, Countifs, Sumif in my Dashboard and it is working fine.

Thanks,

A.M

Re: CountIf with Dates

Former Member — Tue, 06 Mar 2012 19:29:35 GMT

Each of the functions listed above should work. A comprehensive list of Excel Funtions that work with Xcelsius 2008 follows:

ABS ACOS ACOSH ADDRESS AND ASIN

ASINH ASSIGN ATAN ATAN2 ATANH AVEDEV

AVERAGE AVERAGEA AVERAGEIF BETADIST CEILING CHAR

CHOOSE CODE COLUMN COLUMNS COMBIN CONCATENATE

CORREL COS COSH COUNT COUNTA

COUNTIF COVAR DATE DATEVALUE DAVERAGE DAY

DAYS360 DB DCOUNT DCOUNTA DDB DEGREES

DEVSQ DGET DIVIDE DMAX DMIN DOLLAR

DPRODUCT DSTDEV DSSTDEVP DSUM DVAR DVARP

EDATE EFFECT EOMONTH EVEN EXACT EXP

EXPONDIST FACT FACTDOUBLE FALSE FIND FISHER

FISHERINV FIXED FLOOR FORECAST FV GE

GEOMEAN GT HARMEAN HLOOKUP HOUR IF

IFERROR INDEX INT INTERCEPT IPMT

IRR ISBLANK ISERR ISERROR ISEVEN ISLOGICAL

ISNA ISNONTEXT ISNUMBER ISODD ISTEXT KURT

LARGE LE LEFT LEN LN LOG

LOG10 LOOKUP LOWER MATCH MAX MAXA

MEDIAN MID MIN MINA MINUS MINUTE

MIRR MOD MODE MONTH N NE

NETWORKDAYS NORMDIST NORMINV NORMSINV NOT NOW

NPER NPV OFFSET OR PEARSON PERCENTILE

PERCENTRANK PERMUT PI PMT POWER PPMT

PRODUCT PV QUOTIENT RADIANS RAND

RANDBETWEEN RANGE_COLON RANK RATE REPLACE REPT

RIGHT ROUND ROUNDDOWN ROUNDUP ROWS

RSQ SECOND SIGN SIN SINH SLN

SLOPE SMALL SQRT STANDARDIZE STDEV STDEVA

STDEVP SUBTOTAL SUM SUMIF SUMPRODUCT SUMSQ

SUMX2MY2 SUMX2PY2 SUMXMY2 SYD TAN TANH

TEXT TIME TIMEVALUE TODAY TRUE TRUNC

TYPE VALUE VAR VARA VARP VARPA

VDB VLOOKUP WEEKDAY WEEKNUM WORKDAY YEAR

YEARFRAC

For future lookup of what functions are supported, Within Xcelsius click on Help and under the Contents tab expand "Working With Data" The Supported Excel Functions are listed in that area.

Re: CountIf with Dates

Former Member — Wed, 07 Mar 2012 13:11:48 GMT

If you want implement sort functionality in Dashboard Design(or xcelsius) you just use small() function

=SMALL(List,nth smallest)

Eg : small(list,1) will give the least value in the list

Re: CountIf with Dates

Former Member — Thu, 25 Jul 2013 12:29:43 GMT

Hi Murali,

This post is old but I would like to give feedback.

I am also unable to use Countifs function in Dashboard. How did you manage to use this function?

Regards

Angad