Date Problem in Webi

[Former Member]

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?

[Former Member]

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!

[mhmohammed]

Glad I was able to help.

Thanks,

Mahboob Mohammed

[mhmohammed]

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

[Former Member]

I appreciate the ideas, but after trying them this isn't fixing the issue either.

The Count(If) option is likely the best option between these two, but I'm running into the same issue where Webi is not calculating these dates at all. Let me give you a better idea of what I'm trying to do.

Under Input Controls, a user selects radio buttons for either "Current Date" or "The Tenth of the Month". If the user selects the Current Date radio button, the following formula is called:

[dateUserInput]=If ReportFilter([varDateUserSelection])="The Tenth of the Month" Then [dateSalesCurrentMonthCutoff]

ElseIf ReportFilter([varDateUserSelection])="Current Date" Then [dateCurrentDate]

As you can see above, there are two date variables that are called depending on the selection chosen. If a user selects "The Tenth of the Month", the [dateCurrentDate] variable (which is just CurrentDate() as a variable) returned is 10/26/2015. [dateSalesCurrentMonthCutoff] equates to the tenth of the current month, formatted as "MM/dd/yyyy" - so it will return another date variable as 10/10/2015. I've run these through an IsDate() formula and they return as 1, so they are definitely being read by Webi as a date.

However, whenever I use the dateUserInput variable in any formulas, the Count/Count(If etc formulas ALWAYS return as a zero. I can replace this variable with just about anything else - CurrentDate(); other variables coded as dates, even other User Input date variables - and it will work. But passing the dates through the radio buttons selection completely nullifies all results - they always return as zero.

Is there some issue with using Radio Buttons to select different date variables?

[mhmohammed]

Hi C Mast,

Thanks for the explanation.

Would you please send a sample screenshot of data with desired results of the Count? I'll try to create sample data and work on the formula.

Thanks,
Mahboob Mohammed

[Former Member]

Here's a table of example data to illustrate my point:

Sale ID	Sale Status	Sale Status Date
100	CMP	10/03/2014
101	PEN	11/16/2014
102	CMP	12/15/2014
103	CMP	1/15/2015
104	CMP	10/11/2015
105	CMP	10/26/2015

The formula I want to create is this:

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

So in this case, I want to Count all [Sale ID] with a [Sale Status] of CMP Between the dates of 11/1/2014 and either [dateSalesCurrentMonthCutoff] (10/10/2015 - if the user selects "The Tenth of the Month") or [dateCurrentDate] (10/26/2015 - if the user selects "Current Date") - depending on what a user selects from radio buttons:

Like I mentioned before in my post above, there is something about running these variables through a UserInput sequence is negating any results. If it worked, the above query would find 3 results for "Tenth of the Month" and 4 results for "Current Date" - but instead I'm getting zero results no matter how I format my formula.

However, the formula works just fine if I plug in any other date variable directly - but that negates the point of allowing the user to select which dates they want to run the report through.

Thanks for the help, I appreciate the assistance.

[former_member211235]

Hi C Mast,

Why don't u create the ToDate([dateUserInput];"MM/dd/yyyy") as a new variable and check its retrieving the results? Split the formula and check. You'll understand where the issue is..

Grtz

-Anila.

[mhmohammed]

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