Complex Formula for Header Date and Year

system · September 18, 2008, 6:10am

Hi

I am trying to implement a formula , but somewhere I am doin wrong.

="Incremental Numbers Of The Branches Between 1 and 275 For the Month of "+Month(Max(ToDate([MIS Account Open Month ID];"MMM"))+" "+FormatNumber(Year(Max(ToDate([MIS Account Open Month];"YYYY")));"####")

The value in MIS Account Open Month ID is like (200804,200706) and it should be read as April 2008 or June 2006.

Please someone give some suggestion or solution to this problem.

Thanks Aniket

aniketp (BOB member since 2007-10-05)

system · September 18, 2008, 6:15am

This may not be that neat.

But why don’t you convert it to a string and trim the last two digits and then with an If Else If logic, bring out the month name in the place of month number?

Jansi (BOB member since 2008-05-12)

system · September 18, 2008, 6:28am

Can you please give me a sample code help , as I am stuck with this problem and not getting how to solve this issue?

Liv the max logic , simply if i want to display month and year what can be done?

Please suggest some code. Thanks for your help

Regards
Aniket

aniketp (BOB member since 2007-10-05)

system · September 18, 2008, 6:34am

Or try this

=FormatDate(ToDate(FormatNumber([date_num] ,"000000") ,"yyyymm") ,"Mmmm yyyy")

.

haider (BOB member since 2005-07-18)

system · September 18, 2008, 6:36am

How crooked my idea is!

Follow what Haider told, as it is just a one step process

Jansi (BOB member since 2008-05-12)

system · September 18, 2008, 6:41am

FormatDate(ToDate(FormatNumber([date_num] ,"000000") ,"yyyymm") ,"Mmmm yyyy")

Haider Can you please explain me whayt is the significance of using “000000”

Thanks
Aniket

aniketp (BOB member since 2007-10-05)

system · September 18, 2008, 7:00am

ToDate function accepts only string. So FormatNumber function is used to convert a number into a string in the needed format. As far as I know =FormatNumber(200804 ,"") will result in 200,804 including thousands operator ‘,’ in the number. To get rid of that and =FormatNumber(200804 ,"####.00") will result in 200804.00 giving two decimal places to the number. So it is up to us to choose either # or 0 without ‘.’ to get the desired format.

Haider, please let me know if I am wrong.

Jansi (BOB member since 2008-05-12)

system · September 18, 2008, 7:20am

Instead of using “,” I have to use “;”:

="Incremental Numbers Of The Branches Between 1 and 275 For the Month of "+FormatDate(ToDate(FormatNumber([MIS Account Open Month ID] ;"000000") ;"yyyymm") ;"Mmmm yyyy")

It is not displaying anything , i think there is some mistake in the code i am using,

Thanks
AniKet

aniketp (BOB member since 2007-10-05)

system · September 18, 2008, 7:49am

I agree as you are in Web-I

But the code you mentioned is right. To test what causes the problem, split the formula into many parts and check it incrementally.

Jansi (BOB member since 2008-05-12)

system · September 18, 2008, 8:04am

Thanks a lot , now its working.

If i want to use Max with this , can it be implemented . I tried using

="Incremental Numbers Of The Branches Between 1 and 275 For the Month of "+Max(FormatDate(ToDate(FormatNumber([MIS Account Open Month ID] ;"000000") ;"yyyymm") ;"Mmmm yyyy"))

Is the above query right ?

Jansi can you please let me know ?

Thanks
Aniket

aniketp (BOB member since 2007-10-05)

system · September 18, 2008, 8:05am

It depends on what your dimension [MIS Account Open Month ID] has in it.

Jansi (BOB member since 2008-05-12)

system · September 18, 2008, 8:42am

It has values like :

200804,
200803
200704

In this manner the data is there in this object./

Thanks
Aniket

aniketp (BOB member since 2007-10-05)

system · September 18, 2008, 8:50am

Ok fine… What do you want in the header actually(as per your requirement)? Is that the maximum value you need? If yes, then go ahead

Jansi (BOB member since 2008-05-12)

system · September 18, 2008, 9:00am

Thanks a lot Jansi.

Regards
Aniket

aniketp (BOB member since 2007-10-05)

system · September 23, 2008, 5:11am

Hi

This is the original problem :

but today i encountered the same problem , sorry for posting the issue again.

I have a field whose type is Number and the values present are 200808,200804,200607,

Now i want to display : Month = Aug 2008 , as the max value present is 200808. So how it can be implemented please help.

Thanks Aniket

aniketp (BOB member since 2007-10-05)

system · September 23, 2008, 7:11am

Till some extent i found the solution.

Steps:

The values for Month is in number : 200801,200802, 200808

Now first i calculated the max , Max([Month Id]) it gave me = 200,808

Then as per the prvious query i used :

FormatNumber(Max([Month Id]);“000000”) it gave me = 200808

Now after that i tried using , ToDate function , but its giving January’s data. and its displaying Januray 2008 but i want to display August 2008I don’t know why it’s happening , and i am struggling with this problem from a long time , please if someone can through some light on this …!!1

Thanks & Regards
Aniket

aniketp (BOB member since 2007-10-05)

system · September 23, 2008, 7:16am

Post your exact formula and try using FormatDate. Previously you got the solution after trying what was suggested in that link right? Also you could have continued there itself instead of opening a new topic for the same problem

Jansi (BOB member since 2008-05-12)

system · September 23, 2008, 7:31am

Ya i know i could have continued that , but i thought if i continue then the 3 days back post will open again , and thats the reason i dint post there.

And the exact formula I am using is :

The below forula gives me : 200808

="Banding Numbers For The Month of "+FormatNumber(Max([Financial Month ID]);“000000”)

Finally i want to display August 2008 , but when i am using todate() function , it is showing 1/1/70 , i think its not taking todate() function ,
so now i got stuck , as the final output with the final formula is

="Banding Numbers For The Month of "+FormatNumber(Max([Financial Month ID]);“000000”) = 200808
And i need it to be August 2008. But nothing seems to work here.

Thanks
Aniket

aniketp (BOB member since 2007-10-05)

system · September 23, 2008, 7:36am

What are you getting if you use this?

=Max(FormatDate(ToDate(FormatNumber([Financial Month ID] ;"000000") ;"yyyymm") ;"Mmmm yyyy"))

Jansi (BOB member since 2008-05-12)

system · September 23, 2008, 7:41am

Its Displaying January 2008.

Till FormatNumber() it’s working fine , but after encountering ToDate() function its not working. That’s why in the end It’s giving January 2008.

Can u plz suggest if any any other alternative is possible or not ?

Thanks a Lot

Regards
Aniket

aniketp (BOB member since 2007-10-05)