9 Replies Latest reply: Jul 1, 2013 1:23 PM by Sophia Carmien RSS

    Chart expression set analysis only works with flat file

      I have an expression that works when my data source is directly from the xlsx file, but not when the xlsx file is imported to a DB which is used as the source data.

       

      Chart expression

      Sum ({<version = {'$(=FirstSortedValue(version, -date))'} >} cases)

       

      This should grab the version associated with the most recent date.  I can't use "Max(version)" instead of "FirstSortedValue(version, -date)" because version is not a number, but in the formate of a program version, e.g. "3.0.1.355".

       

      Load statement that makes the chart expression work

      LOAD date,

           version,

      FROM

      [\\ftp1\VersionDates.xlsx]

      (ooxml, embedded labels, table is Sheet1);

       

       

      Load statement that makes the chart expression NOT work

      Load

          Date(date, 'MM/DD/YYYY') as date,

          version;

      SQL

          select

              convert(varchar(10), date, 120) as date,

              version

          from DB1..Version_Dates;

       

       

      Any help would be greatly appreciated!