1 Reply Latest reply on Dec 1, 2010 1:44 PM by . Danihp

    COUNTIF and more

    Sergey Benke

      I have a large dataset (several million rows). It is monthly data. The dataset would have these sample dimensions: Region, Date/Time, Exclusion Reason, Account ID.

      I need to analyze Current month accounts with "Exclusion Reason" = "Active". I do not care if these accounts had "Exclusion Reason" = "Active" in Prior Month but ... I want to concentrate on "Active" in Current Month with "Any other Reason" in Prior Month. "Exclusion Reason" could be: "Inactive", "Closed", "Zero Balance" and so on.

      What is the best way to dynamically (less hardcoding) analyze this LARGE dataset? I can not look Account by Account, even if I limit the set by Region and so on.

      I could transpose my dataset and have two columns: accounts_t1 and accounts_t2 and use Excel COUNTIF statement to flag accounts found in both time periods but I checked Tableau knowledge base and could not find a good alternative to COUNTIF in Tableau.

      Thank you.

        • 1. Re: COUNTIF and more
          . Danihp

          Hi Sergey,

           

          create a set named 'active accounts previus month' and then filter accounts with this set may be a solution.

           

          To do this on v6 follow this steps:

           

          1) Add account and date to Sheet. Month on columns, account on rows.

          2) Create a filter for previus month. At this time you only view target account.

          3) Click down on Account dimension on rows card. Click 'Filter'.

          4) A modal window appear, only 'target' accounts are selecteds. Click OK.

          5) On Account dimension on filter card, click down and select 'create set' on contextual menu.

          6) Change 'Set 1' to a new great name like 'active accounts previus month'

          7) At this time you have your targets accounts on a set called 'active accounts previus month'

           

          Last step: Filter for current month, for Exclusion = active and drag 'active accounts previus month' to filter card.

           

          see you.