7 Replies Latest reply on Oct 18, 2016 4:58 AM by sneha simha

    Dates Calculation

    sneha simha

      Hi All

                       I have a set of some 13 parameters which are required for various graphs and the data i have is for 6 months ie from April 2016 to September 2016.I want my dates in such a way that when i click on May2016, it should show data which comprises of April and May 2016.Attaching the sample file which has only 3 months data.Kindly help me at the earliest.

       

       

       

      Regards

      Sneha

        • 1. Re: Dates Calculation
          Hima vardhan Reddy Pavuluri

          Hi ,

           

          Create a parameter with type as date  like this :

           

          10182.PNG

           

          Create a calculation like this :

           

          INT(MID(STR([Year Month]),5,2))<=DATEPART('month',[Parameter 1])

           

          Now use this calculation as filter to restrict data only for month which is equal to or less than the selected month.

          1018.PNG

          Hope this helps you.

          2 of 2 people found this helpful
          • 2. Re: Dates Calculation
            sneha simha

            Hi Hima,

             

                               Thanks yes this indeed helped me in achieving the data for the month i want, now if i choose Month as Sept then this should have the values from April till Sept i meant that the values should be a sum of data from April+Data from May+Data from June+Data from Jul+Data from Sept.Kindly guide me in the same way as before.

             

            Regards.

            Sneha

            • 3. Re: Dates Calculation
              Hima vardhan Reddy Pavuluri

              Hi Sneha,

               

              INT(MID(STR([Year Month]),5,2))<=DATEPART('month',[Parameter 1])

               

              when you drag this calculation into filters shelf and select true in a sheet , that sheet will have data from the selected month and less than that month ( because we have used <= ). So if you select sept in parameter this filter will restrict your sheet to have data from sep,aug,july,jun,may,april. So if you drag some measure value into the sheet by default it will give you the aggregate data (sum) of all these months.

               

              Hope it helps you .

              2 of 2 people found this helpful
              • 4. Re: Dates Calculation
                sneha simha

                Hi Hima,

                                  The values are not changing from June to Aug it remains the same.M really getting confused.Can you please share me your mail id so that i send across my Workbook .

                 

                 

                Regards

                Sneha

                • 5. Re: Dates Calculation
                  sneha simha

                  Hi Hima,

                   

                                      M really sorry it worked  i had Month as another filter and hence the issue.Superb Solution .

                   

                  Thank you so much it helped.

                  Another query  .I have this formulae (=(( Score)x(0.3))+((Total)x(0.15))+((% )x(0.25))+((Bday%)x(0.15))+((Anniv %)x(0.15))  here the challenge is such that if any one of the mentioned parameter has a null value ex Score has zero, it should count as 1.This is applicable to all the other parameters.Kindly help.

                   

                   

                  Regards

                  Sneha

                  1 of 1 people found this helpful
                  • 6. Re: Dates Calculation
                    Hima vardhan Reddy Pavuluri

                    I hope i understand your question correctly , if i am correct i can suggest you following calculation.

                     

                    Your question is if any one of the parameter is having zero value or lets say NULL then your total result should be 1. other wise it should calculate as per the logic mentioned in calculation.

                    Try like this , check first if any of the parameter is zero or NULL.

                     

                    if score=0 or score=NULL or Total=0 or Total=Null or %=o or %=NULL or Bday=0 or Bday=NULL or Anniv=0 or Anniv=NULL

                    then 1

                    else (( Score)x(0.3))+((Total)x(0.15))+((% )x(0.25))+((Bday%)x(0.15))+((Anniv %)x(0.15).

                     

                    Hope this is your question. You can reach me on himavardhan.ba@gmail.com for any help.

                    2 of 2 people found this helpful
                    • 7. Re: Dates Calculation
                      sneha simha

                      Hi Hima

                                         Yes thank so much.

                       

                      Regards

                      Sneha