| Author |
Message |
nhopp4
Groupie
Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
|
  
Topic: Make 0 Value blank
Posted: 23 Sep 2013 at 9:09am |
Hi guys,
I am trying to take a duration range between 1-7200 anything before or over that I dont want to use because it will skew my averages.
The formula displays right however, it puts in a 0 when it dont meet the conditions. I would like it to be blank. Any ideas?
Thanks!
Nick
|
 IP Logged |
|
|
|
nhopp4
Groupie
Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 23 Sep 2013 at 9:11am |
Numbervar Target; if {@duration} >1 AND {@Duration} < 7200 then Target:={@Duration}
This was the formula not sure why it changed it Edited by nhopp4 - 23 Sep 2013 at 9:12am
|
| IP Logged |
|
nhopp4
Groupie
Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 23 Sep 2013 at 9:24am |
Got it! For anyone getting a "Number is required here"
|
| IP Logged |
|
nhopp4
Groupie
Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 24 Sep 2013 at 10:10am |
I may have spoke too soon here. I am not getting any numbers above 999 on my cross tab does converting that to a string not like the ","? Does anyone have a better way to make the 0 a blank?
Thanks,
Nick
|
| IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 24 Sep 2013 at 10:52am |
your formula should work fine if {@Duration} > 1 AND {@Duration} < 7200 then ToText( {@Duration}) else "" if your not getting any numbers above 999, it could be your {@Duration} formula.
|
| IP Logged |
|
nhopp4
Groupie
Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 25 Sep 2013 at 6:55am |
I also noticed that when I change it to a string and use it in my cross tab I cannot pull an average anymore. My main goal is to have outside the range to display a blank and still be a numeric variable.
Thanks for replying!
|
| IP Logged |
|
kostya1122
Senior Member
Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
|
Posted: 25 Sep 2013 at 7:40am |
try formula 1 if {@Duration} > 1 AND {@Duration} < 7200 then ToText( {@Duration}) else "" formula 2 tonumber(formula 1) but this might not work and give you 0 instead of blank.
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 26 Sep 2013 at 5:23am |
|
I don't know, since I don't use cross tabs much...
but does this work?:
if {} >= 1 and {} <=7200 then
{}
else
{@null}
with @null being a formula that is empty...like:
formula null:
that's it nothing.
usually when a value is null, it is not counted in aggregates
found the null formula on another website...
if this gives an error on saving, try:
tonumber({@null}) //this is what was on the other website, but it doesn't make sense...at least not at first glance.
HTH
|
| IP Logged |
|
Traceyc
Newbie
Joined: 16 May 2013
Online Status: Offline
Posts: 5
|
Posted: 03 Oct 2013 at 3:46pm |
|
Ok, if I understand what you want exactly, I would do two things. First you will need to format field in the data area of your crosstab and select Display String. The formula will be something like this:
if currentfieldvalue = 0 then "" else totext(currentfieldvalue,"#,###")
Now, if you have inserted a column in your crosstab to calculate the average and you want to exclude the zeros from the calculation, then on the calculated member add a value formula something like this:
local numbervar i;
local numbervar avg;
local numbervar cnt;
for i := 0 to CurrentColumnIndex-1 do
(
if GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex) > 0 then
(avg := avg + (GridValueAt(CurrentRowIndex, i, CurrentSummaryIndex));
cnt := cnt + 1; )
);
avg/cnt;
Hope this helps.
|
| IP Logged |
|
nhopp4
Groupie
Joined: 03 Oct 2012
Location: United States
Online Status: Offline
Posts: 62
|
Posted: 11 Oct 2013 at 7:06am |
Thanks guys. Lockwelles advice worked. I created a Null formula with nothing in it. Then my formula one needed the extra tonumber( {@null}). Worked nicely!
Thanks to all who responded!
Nick
|
| IP Logged |
|
|
|
