1 2 Previous Next 15 Replies Latest reply on Feb 8, 2019 12:25 AM by shwetha parashurama

# Calculated field for Number of days in month, where month is selected from Month Parameter

Hi All,

I need your help for calculating number of days in Month. Here month is selected from Month parameter. For ex:Jan 2019,Feb 2019 etc

Regards,

shwetha

• ###### 1. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

Hi Shwetha,

So I've assumed your parameter is a string, so first converted it to a date (if it's a date then ignore that step)

Then this formula should get you the days in the month

[Days in Selected Date]

DATEPART(

'day',

'day',-1,

)

)

It's adds a month to the selected month, goes back on day and then gets the day part for that date

• ###### 2. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

Hi Simon,

Thank you so much for reply.

Yes. I have converted to Date using Dateparse function as below.

DATEPARSE("MMMyyyy",[Reporting Month:])

After that i used your calculation.

But am not getting correct result.

I have attached screenshot for reference.

Regards,

shwetha

• ###### 3. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

I think you just need to add the '01'+ to the month string

DATE(DATEPARSE('ddMMM yy','01'+[Date]))

and then add the "dd" to the PARSE format.

• ###### 4. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

Hi Simon,

I have changed DateParse format as below

DATE(DATEPARSE('ddMMMyyyy','01'+[Reporting Month:]))

But still no changes.

I have attached screenshot for reference.Kindly help me to troubleshoot the issue!!!

Regards,

shwetha

• ###### 5. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

I think you just need a space in the Parse format

DATE(DATEPARSE('ddMMM yyyy','01'+[Reporting Month:]))

You are telling Tableau, in the string 01'+[Reporting Month:] where it will find the date elements, so if your [Reporting Month:] has a space between month and year, this needs to be reflected in the dateparse format

• ###### 6. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

Hi Simon,

Yes. But in this case, the space is only in display and there is no space in value.

I have attached screenshot of parameter [ReportingMonth] for reference.

And below is my calculation for counting days in month

DATEPART(

'day',

'day',-1,

)

)

Thanks again for your support and help!!!

Regards,

shwetha

• ###### 7. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

In which case the dateparse should work. I think (looking back at your screen shot) is that you are SUMing it up.

So this is a nested calculation. Let me explain what each part does

[Days in Selected Date]

DATEPART(

'day',

'day',-1,

)

)

In Bold: This adds one month to the date (so if the Parameter Date is 01/01/2019 this returns 01/02/2019)

[Days in Selected Date]

DATEPART(

'day',

'day',-1,

)

)

In Bold. This takes one day off the date from above (in our example the first nest returned 01/02/2019. This takes one day off that so returns 31/01/2019)

[Days in Selected Date]

DATEPART(

'day',

'day',-1,

)

)

In Bold. The final part gets the Day from the above date (in our example the first nest returned 01/02/2019, the second nest returned 31/01/2019. This one takes the day so returns 31

So if you are using a SUM, this is like every row having the number 31, and then SUMing those rows up.

Hope that makes sense

1 of 1 people found this helpful
• ###### 8. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

Hi Simon,

Thank you so much for your explanation. Its clear for me now.

But when i drag that field ,it is automatically summing up. How can i prevent it?

I tried to change it to count, but it does not work.

Regards,

shwetha

• ###### 9. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

Hi Swetha,

Try this one. It's work for sure.

Regards,

Prabhakar.

• ###### 10. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

Hi Prabhakar,

But am unable to open file. Can you please save the sheet for Version  2018.1  compatibility?

Regards,

shwetha

• ###### 11. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

Hi Shwetha,

Regards,

Prabhakar.

• ###### 12. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

Hi,

Please find the below attachment and Screen shots if it is working then marked as correct or helpful.

Thanks,

Amar D. Savale

• ###### 13. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

What do you want the aggregation level to be? Avg, Min, Max ...etc?

You can right click on the field in your view (the green pill) and you can

change the aggregation here. Or you can change it to a dimension, which

will have no aggregation at all and just bring in the value (31, say from

my example). In here you can also change it to discrete (blue pill/header)

or continuous (green pill as you have now)

On Fri, 8 Feb 2019 at 02:58, shwetha parashurama <

• ###### 14. Re: Calculated field for Number of days in month, where month is selected from Month Parameter

Hi Simon,

It works for me once i changed it to Dimension.

Thank you so much !!!

Regards,

shwetha

1 2 Previous Next