Skip to Content

Count in between max date and min date

Hello Experts,

I have a requirement in webi 4.2 SP3.

I want to get the count of product in last week. Data source is MS_Excel. Made the variables as below:

Max date = max([date_field]) : In source the maximum date will be the last date of the last week.

Min date = relativedate(max([date_field]);-6)

The issue is here:

=count(prod) where([date_field]>=Min date and [date_field]<=Min date) - this throws #ERROR

Please give some input.

Thanks in advance

LKumar

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jun 20, 2017 at 03:20 PM

    what is the datatype of date_field object?

    use below formula for max date

    Max date = max([date_field]) in report

    in your formula you have used Min date twice for calculation. is it right?

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 20, 2017 at 03:39 PM

    Thanks Amit.

    datatype is date.

    In formula, it was a typo. Below is the correct one tried.

    I didn't try the formula you have shared. But Max date and Min date are showing as expected. The result of below formula makes headache.

    =count(prod) where([date_field]>=Min date and [date_field]<=Max date)

    Kindly let me know.

    Add comment
    10|10000 characters needed characters exceeded

    • try to break the formula and see.

      =count(prod) where([date_field]>=Min date )

      =count(prod) where([date_field]<=Max date)

      check where is the issue.what is prod ?