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

# Dates Calculation

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

Hi ,

Create a parameter with type as date  like this :

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.

Hope this helps you.

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

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

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

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

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

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

Hi Hima

Yes thank so much.

Regards

Sneha