Tally Table Uses - Part I

Question

Tally Table Uses - Part I

Sioban Krzywicki

One Orange Chip

Points: 27770
More actions
July 31, 2010 at 1:28 pm

#221981

Comments posted to this topic are about the item Tally Table Uses - Part I
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 1

It IS interesting to see what folks do with the Tally Table or the "pseudo cursor" methods that are used to build it.

Nice article and lot's of code to play with. Thanks. Now, I have to go back and play with the code along the way. I'm sometimes amazed at what some accountants/companies will go through in the name of a fiscal year. I have to admit that your requirements are fairly odd although quite easy to understand.

Thanks for all the kudos on the Tally Table article, Stefan. I'm glad I could help and it's good to see so many folks carrying such a useful technique forward. Heh... not sure I deserve all that, though... lot's of folks were using a "numbers" table a very long time before my article. 😛

Looking forward to Part 2...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

Heh... pretty low marks for the article so far, Stefan. Although I certainly appreciate what you tried to do both in your code and for the Tally Table, I think that people read "Tally Table Uses" as an article title and expect to see a plethora of code snippets for different purpose uses of the Tally Table across a wide spectrum. Instead, they find a fairly peculiar calendar table with a very limited use (one company) which also requires manual intervention every couple of years that just happens to use a Tally table as a driver. Again, I appreciate both attempts but the public apparently has a different take on the article. I'm telling you this because you may want to reconsider what you publish as Part 2. Give it a shot...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 3

Jeff Moden (8/1/2010)
It IS interesting to see what folks do with the Tally Table or the "pseudo cursor" methods that are used to build it.
Nice article and lot's of code to play with. Thanks. Now, I have to go back and play with the code along the way. I'm sometimes amazed at what some accountants/companies will go through in the name of a fiscal year. I have to admit that your requirements are fairly odd although quite easy to understand.
Thanks for all the kudos on the Tally Table article, Stefan. I'm glad I could help and it's good to see so many folks carrying such a useful technique forward. Heh... not sure I deserve all that, though... lot's of folks were using a "numbers" table a very long time before my article. 😛
Looking forward to Part 2...

Perhaps they were using it before you, but you're where I learned it so you get the thanks. : -)

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 4

Jeff Moden (8/2/2010)
Heh... pretty low marks for the article so far, Stefan. Although I certainly appreciate what you tried to do both in your code and for the Tally Table, I think that people read "Tally Table Uses" as an article title and expect to see a plethora of code snippets for different purpose uses of the Tally Table across a wide spectrum. Instead, they find a fairly peculiar calendar table with a very limited use (one company) which also requires manual intervention every couple of years that just happens to use a Tally table as a driver. Again, I appreciate both attempts but the public apparently has a different take on the article. I'm telling you this because you may want to reconsider what you publish as Part 2. Give it a shot...

If they just don't find it useful, I don't mind so much. There are some good techniques (I think) in there for any kind of non-standard calendar and it only requires manual intervention if the calendar is changed by the business people. I put that part in to show how easy it is to make that kind of manual intervention. I thought the subtitle was pretty clear that article I only handles fiscal year calculations.

Where I'd worry is if people started tearing the code apart, telling me I got things wrong, then I'd have to go back and reexamine things and fix things. Even then, I wouldn't so much "mind" as be chagrined. I do appreciate the suggestion, but I'm going to let it stand. I can't count the number of times I've read code or a coding technique and thought I'd never use it and later found how useful it was.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 5

Jeff Moden (8/1/2010)
It IS interesting to see what folks do with the Tally Table or the "pseudo cursor" methods that are used to build it.
Nice article and lot's of code to play with. Thanks. Now, I have to go back and play with the code along the way. I'm sometimes amazed at what some accountants/companies will go through in the name of a fiscal year. I have to admit that your requirements are fairly odd although quite easy to understand.
Thanks for all the kudos on the Tally Table article, Stefan. I'm glad I could help and it's good to see so many folks carrying such a useful technique forward. Heh... not sure I deserve all that, though... lot's of folks were using a "numbers" table a very long time before my article. 😛
Looking forward to Part 2...

And before I forget, thanks for the compliments above.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

Stefan Krzywicki (8/2/2010)
I do appreciate the suggestion, but I'm going to let it stand. I can't count the number of times I've read code or a coding technique and thought I'd never use it and later found how useful it was.

I have a great appreciation for that and my hat is off to you for making such a commitment to others. Well done!

Like I said, I look forward to Part 2... even the original one. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 7

Jeff Moden (8/2/2010)
Stefan Krzywicki (8/2/2010)
I do appreciate the suggestion, but I'm going to let it stand. I can't count the number of times I've read code or a coding technique and thought I'd never use it and later found how useful it was.
I have a great appreciation for that and my hat is off to you for making such a commitment to others. Well done!
Like I said, I look forward to Part 2... even the original one. 🙂

Well, it'll have to be the original once since it is running tomorrow : -)

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 8

I thought this was interesting, and I've dealt with this in a few places. If you don't have to work with the financial system, you might not encounter this. It seems CPAs always want things to end on a Friday, or some off period, not just letting things end on whatever day the month ends.

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 9

Steve Jones - Editor (8/2/2010)
I thought this was interesting, and I've dealt with this in a few places. If you don't have to work with the financial system, you might not encounter this. It seems CPAs always want things to end on a Friday, or some off period, not just letting things end on whatever day the month ends.

Thanks. As a consultant, I've seen this kind of table at a number of places and they're all different. Worse, most have the dates either updated by hand or in Excel and then imported.

I likely should have pointed out in the article one of the reasons I listed this as a Tally Table article instead of just a Fiscal Period article. When I dealt with this kind of think before Tally Tables, it always came down to cursors and there's a big change in how you think about the problem with a Tally Table. With a cursor, you think about looping through the data and start setting up variables for incrementing the differenct results you'll need. Then you set your maximum values for the various types: quarters, periods, etc... Not only does it run slower, it becomes quite a bit to keep track of and it isn't easy to change when the inevitable requests come in to tweak it a bit.

With the tally table, the variables go away and you think about math. Create a formula for each column and go! When the change requests come in, you either tweak the formula or, if it is a one-off, you do as I did in the article, change the start points and everything zooms along.

The switch in thinking probably saves me more time than the improvement in run speed. And now I have code I can bring with me from assignment to assignment that's actually worth bringing.

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 10

thx

i've read tally table articles here for the last few years and never thought i had a use for them. last 6 months i've been building a performance data warehouse but haven't done too much in getting use out of the data. going to build a tally table into it just to divide the data into weeks, months, etc to see if we can find trends later on

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 11

I've always had to build a separate table and maintain it. Something like

Period Date

1 1/1/2000

1 1/2/2000

...

1 1/28/2000

2 1/29/2000

...

Carolyn Stern Right there with Babe Points: 767 More actions · Answer 12

Hi - Actually this isn't a very specific use of a tally table for one customer.

This is called the "Retail Calendar" and is used by pretty much every retail business including the one I work for. We have retail stores and we have a large factory.

You can google it and look it up.

I don't know if this person is on the retail calendar or if he just has a weird fiscal set up.

I have been using a similar tally table for several years. One of the things I include is whether that particular date is a "working factory day". That doesn't mean to exclude sat/sun/holiday. As we approach the holidays we often have sat/sun where the factory works and ships product. So after the fact we add a Y if the factory worked that day.

Sioban Krzywicki One Orange Chip Points: 27770 More actions · Answer 13

Carolyn Stern (8/2/2010)
Hi - Actually this isn't a very specific use of a tally table for one customer.
This is called the "Retail Calendar" and is used by pretty much every retail business including the one I work for. We have retail stores and we have a large factory.
You can google it and look it up.
I don't know if this person is on the retail calendar or if he just has a weird fiscal set up.
I have been using a similar tally table for several years. One of the things I include is whether that particular date is a "working factory day". That doesn't mean to exclude sat/sun/holiday. As we approach the holidays we often have sat/sun where the factory works and ships product. So after the fact we add a Y if the factory worked that day.

Interesting, this is for a retail company, but I don't know if they are on a standard "retail calendar" or if they've made up their own that is just very similar. Does the standard one have the "correction" of an additional week that I entered?

Thanks for the information!

--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 14

wish i just had a tally table

bought my wife an iPhone 4 just now and needed to know which days were week 30 of 2010 since that when her phone was manufactured. rumor is that the latest iphones have a matte coating on the antennal to avoid the death grip issue

Apple uses the week of manufacture as part of the serial number in all of their products