1 Reply Latest reply on Jan 16, 2014 10:43 AM by Phillip Burger

    Can someone help me how to get the value (exact date) from the formula when I use max (date)

    Chaitanya K

      Hi,

       

      I am using a formula to get maximum date.

      I have created a calculated field as Maxdate = max(date_) where "date_" is the date column in database.

      But when I use the below formula to calculate 30 day it is throwing me error as "cannot mix aggregate and non aggregate functions as argument" .

       

      if [date_] <= [Maxdate] and [date_] > [Maxdate]-30 then 'Last30Days'

      elseif [date_] <= [Maxdate]-30 and [date_]> [Maxdate]-60 then 'Previous30Days'

      else null end

       

      so, if i can get the exact value of the maxdate in format mm-dd-yyyy it will solve my problem.

       

      is there anyway to get that??

        • 1. Re: Can someone help me how to get the value (exact date) from the formula when I use max (date)
          Phillip Burger

          Hi, Chaitanya. The attached workbook contains a solution. I used the database to learn the maximum date.

           

          I also tried defining my maxdate as max(date_prod) in a calculated field. It didn't return the maximum date encountered in all of the data. Rather, it evaluated each record as the domain to check for the maximum date. I'm not sure why.

           

          Re the "cannot mix aggregate and non aggregate functions as argument" error, I was able to overcome this by experimenting with continuous vs. discrete.

           

          I used Postgres 9.3 as my database. Here is the custom SQL code I used in my data connection:

           

          SELECT "films"."code" AS "code",

            "films"."title" AS "title",

            "films"."did" AS "did",

            "films"."date_prod" AS "date_prod",

            "films"."kind" AS "kind",

            "films"."len" AS "len",

            (select max("films"."date_prod")

              from "public"."films" "films") AS maxdate

          FROM "public"."films" "films"


          To help you receive faster help in the future, you'll want to post questions of this nature in the Forum rather than in Viz Talk.