I have a date based report, which has either one or two records per day. If a day has two records, one will have a value for one of the columns, and the other will not. My goal is to hide the row without a value for the days that have two records.
I found an older post that talks about accomplishing this by creating a variable which uses the Previous command. It would return a value of 1 for rows which are unique (have a different date than the previous record) and a 0 for rows which contain the same date as the previous record. I would then create a filter to hide the rows with a value of 0.
I have created the variable and added it to my report. The issue I have is that the records appear on my report with the duplicate record with no value listed first, and the record with a value listed second. So the duplicate record with a value gets assigned a value of 0, and would be hidden by the filter (see attached report sample).
I applied a sort to the report to reverse the order of the duplicate date records, thinking that would resolve the issue, but even though they now appear in the right order, the variable still has a 0 value for the record with the value. I’m wondering if the report calculates the variable values before the sort is applied?
I would appreciate any assistance you can provide on how I can resolve this issue.
couldn’t you just sum them or use the max function? It looks like those are set up as dimensions, which is why this is happening, should be measures with aggregation sounds like (without having all the details)
[quote:bc1e712007=“erik.stenson”]couldn’t you just sum them or use the max function? It looks like those are set up as dimensions, which is why this is happening, should be measures with aggregation sounds like (without having all the details)
[/quote]
You are correct, simply wrapping =Max() around my field resolved the issue. I was put in charge of Personas last year and I’m losing what WebI skills I had!
I have one other issue that I can’t figure out. Using the example below, I need to count the number of days between data occurrences in the column on the right. Plus I need to ignore the first value on the right as I won’t have a previous value to determine the number of days for it.
So in this example, 5/24/16 would not have a Days value, since it is the first date to have a value. 5/25/16 would have a value of 1, and 5/31/16 would have a value of 6. All of the other rows, including 5/24/16, would have a value of 0.
I know this may be getting pretty complex for WebI, but is there a way to do it? I’m trying to recreate an existing Excel report in WebI, as the Excel report has become unwieldy because of the amount of data we have collected.
If isdatatype([Col 3 value]), then days between(previous[Col 1 date];[Col 1 date])
Which isdatatype is used depends on what the contents of col 3 are - text, numeric or date.
The other thing you may need to consider is whether the data relates to a single key record for example, a person, so you may need to take the key record ID into account as well when calculating your date differences as the col 3 data may span a single ID or two.
I can make your suggestion work, using ToNumber([Col 3 value]) > 0 for the If condition.
But I always get a value of 1 back for the DaysBetween value, since Previous([Col 1 date]) is always one day back.
What I need is a variable that tracks the last date that had a value in Col 3, but I can’t figure out a way to do that. I’ve tried doing things like creating a variable called var_LastDate and creating a formula like If (previous Col 3 value > 0 Then previous Col 1 date Else previous var_LastDate).
However, I can’t write a formula like that as it won’t let me use a variable within its own formula.
So, if you have an idea how I can somehow store the last previous date that had a value in column 3, that would be great.
(BOB member since 2002-08-20)
(BOB member since 2009-01-09)