Summary and variable question

Ok, I’m absolutely sure this is a dead simple question with an easy answer but my brain isn’t figuring this out.

How do I Total a column of numbers and then assign that total to a variable? Specifically, I want to be able to total a column of market values for a list of stocks and then create a permanent variable with the total market value to be used in various calculations.

I’m going to sit down and watch the variable training video tomorrow, but it’s 2 hours long and I don’t have the time to sit through it tonight. If anyone has a quick and easy answer for me, I’ll be forever grateful!

Thanks,

Peter

Take a look at the aggregate( function.

1 Like

Will do. Thanks. That’s one I haven’t used yet.

Peter, using a permanent variable would be rare. I think you would ordinarily use the narrowest variable you need, e.g., local, fileglobal, global (and other types). Permanent variables are fileglobal variables, but they are saved with the database and are available when you re-open the database. If you are generating the variable value equal to the total of a column, you can just regenerate that the next time you open the database and you may not need a permanent variable.
If you were doing all of your calculations in the same procedure in which you get the total you want, you could do it with a local variable. But if you want to use the result in a form or in a subsequent procedure you would need a fileglobal variable. In a procedure, you could use this:

letfileglobal VARNAME=aggregate({FIELDNAME},"sum",
  {Your query if less than all the records are wanted})

If you do wnt to create a permanent variable, use the letpermanent statement instead of letfileglobal.

Thanks. Good point. I had kind of assumed that if I had a variable that would update occasionally but would be persistent when the database was closed, I should use a permanent variable and save the trouble of recalculating each time. But since it’s easy and takes no time to rerun the calculations for it, I think the local variable may work fine. Going to do some experimenting but thanks for pointing this out.

Peter

I am really pleased to see the aggregate( function mentioned here. I was really excited when I added this function to Panorama X, as it makes it trivial to calculate totals, averages, etc. on the fly, where before you would always have to go thru the whole process of creating and calculating with summary records. Unfortunately I don’t think the aggregate( function has gotten as much traction as I hoped it would, which is probably a bit of a marketing failure on my part. So I encourage anyone that hasn’t looked into this function to check it out.

Tom mentioned creating a fileglobal variable using the aggregate( function to display a sum in a form. But keep in mind if the database is less than 100k records or so, you can probably just use the aggregate( function directly in a Text Display object, with no variable needed at all!

Thanks! I’m really glad someone mentioned the aggregate( function because it has worked perfectly for what I was trying to do. And yes it’s underuse may just be a marketing issue because I had no idea it even existed before this thread.

Which does raise the one challenging issue regarding PanX documentation, which is that while much is documented very thoroughly, it is sometime hard to find what you’re looking for if you don’t know the exact name of the term. So while I had searched for a lot of items using the terms “variables”, “summaries”, etc etc; but because I didn’t know the Aggregate function existed, I never stumbled on it.

Not sure what the solution is; probably more cross-referencing in the Help files at least. One more thing to do in all your free time.
Thanks,

Peter

I think the aggregate( function is covered in the Intensive Training videos, but it’s in the lookup video, not the session about variables.

Well, the intensive training videos are great and I’m happy to have paid for them and have gotten a lot from them. But If you need to look up something quickly, videos are not the way to go. And not being a professional PanX developer myself, finding the time to sit through all the hours of video is hard, not to mention trying to retain all the content when I’m not using it every day.

I know printed manuals are old school and pretty much gone with the wind, but I still like having something with an index I can flip through. Books have hung on for hundreds of years because they are efficient. (I still have my copy of the Pan 6 manual on my shelf, though admittedly it’s been years since I cracked it open and it is no doubt is increasingly irrelevent.)

Second best would at least be pdf version of a manual, perhaps as a complement to the online help. But I know how much work that is for relatively few users probably.

Peter

I wonder if you mean the Panorama 3 manual, which really was the Panorama II manual plus a separate supplement.That was the last printed documentation that came “in the box” with Panorama.

It was possible to purchase printed Panorama 6 manuals, so maybe you do have one on the shelf. I say “manuals” plural because it wasn’t just one manual, but a set of seven different manuals that cost a few hundred dollars for the complete set (several thousand pages). Naturally very few users purchased even one volume of this set, which helped make the decision to completely discontinue printed manuals a no-brainer.

We got so many complaints that earlier printed and PDF versions of the manual were too long! Very discouraging after all the work that went into producing them. Of course the Panorama X documentation is actually even longer, but that is kind of disguised by the fact that there is no way to know the actual page count (I don’t even know what it is).

For tech support, and here on this forum, it has been a godsend to be able to simply provide a direct URL link to a topic. Most users don’t want to read a book, they just want the answer to their immediate question, and now we can point them right to that. In the past we would have to point to a page number in a PDF file, and that would often engender the question “how do I get this PDF file?” “Where do I find that?” even though the PDF files were automatically installed with Panorama. Now that is no longer a problem.

By the way, I think perhaps an underappreciated advantage of Panorama X’s online documentation is the hundreds of animated illustrations. Can’t do that in a book or a PDF file. This combines the quick access and searchability of textual documentation with the “watch how it’s done” advantages of video. Creating hundreds of animated images took a lot of time, but I think it was well worth it.

Unfortunately, the last Panorama manual with an index was Panorama 3, almost 25 years ago. Creating a good index is an incredibly time consuming (i.e. expensive) process, and one we haven’t been able to afford for a long time. The good news is that now you can instantly do a full text search of the entire documentation. For example, doing a full text search for “total” or “sum” will return aggregate( as one of the results. Yes, you’ll get about a hundred results to sift through, but that’s a lot faster than trying to look through everything.

You don’t need to tell an Apple developer that! Apple is famous for not providing any documentation beyond a WWDC video to document a complex API.

On the other hand, videos are an excellent way to get a sense of what is available, so that later, you can hopefully remember “wasn’t there some feature that could calculate totals on the fly?” You probably won’t remember the details, but hopefully you’ll remember that a feature exists and then you can search for it, or ask about it here. At least that is how I use video training materials like Lynda.com.

At the end of the day, there is unfortunately no shortcut to mastering a powerful technology like Panorama. I want Panorama to be as easy to learn and master as possible, and sometimes I’m discouraged thinking about how much Panorama users need to learn to get the most out of it. But then when I go to learn some other technology out there, for example an Apple API, or a web technology, I see that this is really an industry wide problem, and that Panorama is really pretty good on this score. It’s always possible to do better, but resource constraints are always an issue as well. Even a large company like Apple really struggles with technical documentation – or try to figure out how to use Google AdSense using Google’s documentation. So I definitely appreciate the problem, and will keep working to make Panorama’s documentation the best that I can.

You’re right, it is the Panorama II and 3 manuals still on my shelf! Serious vintage!

Peter

i was unaware of the aggregate( function.
i’ve always just done something like
field whatever
total
lastrecord
somevariable = whatever
crude but serviceable.