2 Replies Latest reply: Nov 2, 2018 11:43 AM by Martin Hamilton RSS

    Compare a passed value and a calculation

    Martin Hamilton

      Hi - am looking at providing an indicator in the dashboard based on comparing a passed value and a calculated one.

       

      My data looks like this:

       

      Account file

      Subscription file - joined using an account number on both tables


      I have attached sample data in a single excel with multiple sheets.

       

      what I wish to do is compare the Total_Active_Subs on the Account Table with an expression which I have created which calculates active subscriptions.

      if the value mismatches then it will show a value 'Split Account' - if it matches then it will show 'Fully Managed'.

       

      Alternatively is it possible to do or better to this within the load script to create a resident table? is it possible to do within a chart?

       

      what is the best way to do it?

       

      thanks

       

      Martin

        • Re: Compare a passed value and a calculation
          Martin Hamilton

          Any ideas from anyone? thanks martin

          • Re: Compare a passed value and a calculation
            Martin Hamilton

            Managed to solve this myself by creating a resident table initially then using an if statement in a table:

             

            ACCOUNT:

            LOAD

                ACCOUNT_ACCOUNT_NUMBER,

                SUB_ACCOUNT_NUMBER,

                ACCOUNT_ACCOUNT_NAME,

                ACCOUNT_TOTAL_ACTIVE_SUBS

            FROM [lib://DATA/SPLITACCOUNT.xlsx]

            (ooxml, embedded labels, table is Account);


            SUBSCRIPTION:

            LOAD

                SUB_ACCOUNT_NUMBER,

                Sub_Number,

                SUB_STATUS

            FROM [lib://DATA/SPLITACCOUNT.xlsx]

            (ooxml, embedded labels, table is Subscription);


            [SUBSCRIPTION COUNT BY ACCOUNT]:


            LOAD SUB_ACCOUNT_NUMBER,

            count(Sub_Number) as SUBS_LOADED_WITH_ACCOUNT Resident SUBSCRIPTION


            Group By SUB_ACCOUNT_NUMBER;


            Followed by:


            if (ACCOUNT_TOTAL_ACTIVE_SUBS = SUBS_LOADED_WITH_ACCOUNT,'No','Yes')