3 Replies Latest reply on Dec 10, 2014 3:04 PM by Chris McClellan

    Count If wildcard

    Dominic Cooney

      Hi,

       

      Very new to this - and only just delving into calculations!

       

      I have some weather data by station, which has a text field giving a forecast/summary of conditions for the day [Significant Weather (Day)]. I want to count the number of forecasts that contain the word snow (as far as I can tell there are 4 distinct variations of this from light to heavy snow), and divide this by the total number of forecasts in each region (each station produces a foreast, which is assigned to a region).

       

      Any idea's, I've gone to the tutorial videos and no luck?

       

      Thanks,

        • 1. Re: Count If wildcard
          Chris McClellan

          Some sample data/twbx would be great, but I'd start by creating a measure with this formula:

           

          if find([Forecast Description], 'snow') > 0 then 1 else 0 end

           

          1 = snow is found in [Forecast Description]

          0 = no snow

          • 2. Re: Count If wildcard
            Dominic Cooney

            Hi Chris,

             

            First of all thanks for the reply; I've not managed to get it working, so I've pasted some of the data below: The column is Significant Weather (Day) which is a text field.

             

                   

            Site CodeSite NameCountryContinentStart TimeDayDateTime StepWind Direction (Day)Wind Speed (Day)Screen Temperature (Day)Significant Weather (Day)Wind Direction (Night)Wind Speed (Night)Screen Temperature (Night)Significant Weather (Night)Relative Humidity (Day)Relative Humidity (Night)Visibility Range (Day)Visibility Description (Day)Visibility Range (Night)Visibility Description (Night)Wind Gust (Day)Wind Gust (Night)Feels Like Temperature (Day)Feels Like Temperature (Night)UV IndexPrecipitation Probability (Day)Precipitation Probability (Night)
            3SOUTHAMPTON AIRPORT(3)ENGLANDEUROPE00:00Tuesday09/12/20140SSW79(White) Medium-level cloudWSW214Heavy Rain8493< 20000 mGood< 10000 mModerate17355112080
            3SOUTHAMPTON AIRPORT(3)ENGLANDEUROPE00:00Tuesday09/12/201424WSW118Sunny intervalsW194(White) Medium-level cloud6777< 40000 mVery good< 20000 mGood23294-112020
            3SOUTHAMPTON AIRPORT(3)ENGLANDEUROPE00:00Tuesday09/12/201448W139Light rainSW346Heavy Rain7190< 40000 mVery good< 10000 mModerate29564212080
            3SOUTHAMPTON AIRPORT(3)ENGLANDEUROPE00:00Tuesday09/12/201472WSW129(White) Medium-level cloudW110(White) Medium-level cloud7086< 40000 mVery good< 40000 mVery good25163-412020
            3SOUTHAMPTON AIRPORT(3)ENGLANDEUROPE00:00Tuesday09/12/201496WNW86Sunny (Day)W90Partly cloudy (Night)7190< 40000 mVery good< 20000 mGood18142-31520
            5LONDON CITY AIRPORT(5)ENGLANDEUROPE00:00Tuesday09/12/20140SSW98(White) Medium-level cloudWSW156Light rain7589< 20000 mGood< 10000 mModerate19314212080
            5LONDON CITY AIRPORT(5)ENGLANDEUROPE00:00Tuesday09/12/201424WSW159Sunny (Day)WSW146Clear sky (Night)6272< 40000 mVery good< 40000 mVery good3130511520
            5LONDON CITY AIRPORT(5)ENGLANDEUROPE00:00Tuesday09/12/201448W179Sunny (Day)SW177Light rain6786< 40000 mVery good< 10000 mModerate3437541580
            5LONDON CITY AIRPORT(5)ENGLANDEUROPE00:00Tuesday09/12/201472WSW1611(White) Medium-level cloudW83(White) Medium-level cloud6882< 40000 mVery good< 40000 mVery good33165012020
            5LONDON CITY AIRPORT(5)ENGLANDEUROPE00:00Tuesday09/12/201496WNW106Sunny (Day)WSW72Clear sky (Night)7986< 40000 mVery good< 40000 mVery good201320155
            6LYDD(6)ENGLANDEUROPE00:00Tuesday09/12/20140SW810(White) Medium-level cloudSW276Light rain8394< 20000 mGood< 10000 mModerate15436212080
            6LYDD(6)ENGLANDEUROPE00:00Tuesday09/12/201424WSW199Sunny (Day)W246Clear sky (Night)6677< 40000 mVery good< 40000 mVery good32395112020
            6LYDD(6)ENGLANDEUROPE00:00Tuesday09/12/201448W2210Sunny intervalsSW348Heavy Rain6788< 40000 mVery good< 10000 mModerate3856541580
            6LYDD(6)ENGLANDEUROPE00:00Tuesday09/12/201472WSW2111Light rainW142(White) Medium-level cloud7686< 20000 mGood< 40000 mVery good36215-316020
            6LYDD(6)ENGLANDEUROPE00:00Tuesday09/12/201496WNW137Sunny (Day)W110Clear sky (Night)7492< 40000 mVery good< 20000 mGood21183-3155
            • 3. Re: Count If wildcard
              Chris McClellan

              Does this help ? (attached)