Skip to Content
avatar image
Former Member

Date Problem in Webi

Hi,

I'm attempting to create a User Input variable where they would select a time range for the report output.

I'm following http://blog.davidg.com.au/2011/09/dynamic-measures-in-webi-on-any-data.html to set up radio buttons where they can select two different dates - the current date or the tenth of the month to run queries through.

I've got the radio buttons working and each selection outputs the correct date. For example, if the user selects the Radio Button called "Current Date", the dateUserInput variable outputs today's date. If the user selects "The Tenth of the Month", the date is output as 10/10/2015. I've confirmed using the IsDate formula that the outputs are read as date.

I'm attempting to run a count where the dateUserInput variable determines the end of the time period:

=Count([Sale ID] Where (([Sale Status]="CMP") And [Sale Status Date] Between([dateReportStartLastYear];ToDate([dateUserInput];"MM/dd/yyyy")));All)

but it's not working. No matter what I run with this variable, it returns 0 results. I have converted it to a date in the formula above, so I doubt that is the issue - the formula is valid in the formula editor.

If I create a simple "Current Date Variable" and replace dateUserInput with it, it works fine. What is the problem with my formula?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Oct 26, 2015 at 06:21 PM

    Hey C Mast,

    I'm positive that I understand your question. instead of that Count, why don't you try to using Sum(If()) or Count(If()) as below, it will give you an idea.

    =Count(If(([Sale Status]="CMP") And [Sale Status Date] Between([dateReportStartLastYear];ToDate([dateUserInput];"MM/dd/yyyy")))) Then [Sale ID])


    or


    =Sum(If(([Sale Status]="CMP") and [Sale ID] = [Sales ID] And [Sale Status Date] Between([dateReportStartLastYear];ToDate([dateUserInput];"MM/dd/yyyy")))) Then 1)


    I'm 100% confident that those formulas have extra/are missing parethesis, please fix it. I was just trying to give you an idea.

    Let me know if that helps.


    Thanks,

    Mahboob Mohammed



    Add comment
    10|10000 characters needed characters exceeded

    • Hi C Mast,

      Anila is on point, that's what I was going to write. The logic though is incomplete if you just use todate(), I was able to achieve it with some updates. I created some sample date, 6 variables, Input Control in a report and Voila, got it working..

      Created the below variables in the orrder

      • v_CurrentDate =CurrentDate()
      • v_DateSelection ="The Tenth of this Month" (This is the one we apply Input Control on & type in the LOVs manually, "Current Date" and "The Tenth of this Month" in your case.)
      • v_FirstOfLastNovember =ToDate("11/01/2014";"MM/dd/yyyy")
      • v_TenthOfThisMonth =ToDate("10/"+FormatNumber(MonthNumberOfYear(CurrentDate());"##")+"/"+FormatNumber(Year(CurrentDate());"####");"MM/dd/yyyy") (I'm sure, the issue was in this formula, I had to use formatnumber() before I could use Todate())
      • v_DateSelectionDate =If([v_DateSelection]="Current Date") Then [v_CurrentDate]

      ElseIf([v_DateSelection]="The Tenth of this Month") Then [v_TenthOfThisMonth]

      • v_CountofIDs =Sum(If([Status]="CMP" And [Date] Between ([v_FirstOfLastNovember];[v_DateSelectionDate])) Then 1 Else 0)

      (Updated v_CountofIDs formula at 10/27/2015 12:12 pm (EST. The snapshots will be a bit off as I deleted a row that had CEP as Status)

      Create the Input Control on v_DateSelection (or whatever you call that variable) with manual LOVs as I said earlier and you'll see that the v_CountOfIDs formula I have will work.

      Here's a snapshot of my data and with Current Date selected

      Snapshot with The Tenth of this Month selected.

      Hope that helps.


      Thanks,
      Mahboob Mohammed

      Snap 02.png (59.1 kB)
      Snap 01.PNG (69.4 kB)
  • avatar image
    Former Member
    Oct 27, 2015 at 05:25 PM

    Thank you so much for all the help Mahboob. As it turns out, most of what I'd done was correct and the problem was something unrelated to what I thought it was. I wouldn't have gotten there without your help, because it was your code that pointed out my problem.

    In the original tutorial here: BOBJ Tricks: Dynamic Measures in Webi (on Any Data Source)

    the code for v_DateSelectionDate variable called for the use of a ReportFilter formula. I didn't put this in the original post because it was cited in the tutorial, but I linked the tutorial incorrectly above.

    So the code I had for v_DateSelectionDate was something like this:

    • v_DateSelectionDate =If(ReportFilter([v_DateSelection]="Current Date") Then [v_CurrentDate])

    ElseIf(ReportFilter([v_DateSelection]="The Tenth of this Month") Then [v_TenthOfThisMonth])

    It turns out the ReportFilter formula was what was negating my counts. After I removed it, all my counts worked as I thought they would have in the first place. Thanks so much!

    Add comment
    10|10000 characters needed characters exceeded