3 Replies Latest reply: Jul 28, 2018 10:12 AM by Andy Manuja RSS

    Retrieve unique number of values to a variable

    Andy Manuja


      I am running a script to calculate the annualized cost based on the given months of data.


      I want to calculate the unique number of months of cost data I got. For example, assume my financial year is from April 2018 to March 2018 and I got the data for April, May and June. Thus, the number of unique months should be"3".


      To get this value in to a variable, I used the below code in my data load editor in a temporary table,

      ('$(Count(DISTINCT Month))') as NoOfMonths


      As mentioned above, the variable is NoOfMonths and I need to use that value to perform my subsequent calculations.


      The Data loading did work fine but the value I am getting for the "NoOfMonths" variable is not correct.


      Could someone help me this issue?


      Awaiting for a reply.


      Thank you in advance.


      Kind regards,


        • Re: Retrieve unique number of values to a variable
          Bala Bhaskar

          May be this:


          ('$(RangeCount(above(Total Count(Distinct Month),0,3)))')


          ('$(RangeCount(above(Count(Distinct Month),0,3)))')

          • Re: Retrieve unique number of values to a variable
            Andy Manuja

            Hi Bala,

            I tried both the codes but unfortunately still it's not giving me the correct answer.

            Let me explain what I am doing may be then it might help you to get a proper understanding.

            I am calculating an annualised amount based on the given input number of month's data.


            Please refer my data set,


            Project ComponentsDivisionAprilMayJune


            The above data set is for a single division and similarly, I got another two divisions with similar data sets.


            The calculation of the annualised amount is = Total Cost of each activity(Analysing, designing, etc) / 3 (which is the number of months) * 12 ( to get the annualised value).


            Please find my code below,



            CrossTable (Month,Costs,2)


                "Project Components",





            FROM [lib://Actual_Data/Actual Division Costs.xlsx]

            (ooxml, embedded labels, table is [Div-A]);



            Load "Project Components",

            Date(Date#(Month,'MMM'),'MMM') as Month,


            Resident Div_A_Actuals;


            Drop Table Div_A_Actuals;



            Load *,

            //Count(DISTINCT Month) as NoOfMonths

            ('$(RangeCount(above(Count(Distinct Month),0,3)))') as NoOfMonths

            From [lib://Actual_Data/Temp.QVD] (qvd);



            At the moment since I got only 3 months data, the variable value of the "NoOfMonths" should be "3". However, according to my commented code and using of your provided code, it still shows the variable value as 15 (which is "5 Project Components" multiplied by 3.


            Hope now it is much clear and look forward to hearing from you.