I can’t get this formula to work (I get result “$,”) but this works fine:
pattern(arraynumerictotal(arraybuild(cr(),“Expenses”,“Amount”,
{yearvalue(Date)=val(2020)}),cr())/arraynumerictotal(arraybuild(cr(),
“Trips”,“Time”,{yearvalue(Date)=val(2020)}),cr()),“$#,”)
I am intrigued as to why the formula using aggregate is not working. I have run it through the formula workshop and this is what I get
Just a thought, but try going through the formula and make sure all the quotes are straight quotes and not any smart quotes. This can sometimes mess up formulas in strange ways.
I have checked that all quotes are now straight (not the case before)
This simple formula applied on the formula tab of a Text Display Object in the Expenses database form:
(aggregate({Time}),’‘Trips’’)
gives me the error
“Expression contains an operand when an operator was expected”
These formula
aggregate({Time}),’‘Trips’’ and this formula
aggregate({Time}),’‘Trips’’)
give me the error
“Commas are not allowed outside of a function”
What do I need to change to pull in the data from Trips?
aggregate({Time},{},{},{Trips})
This will total the Time field in the Trips database. I have used all brackets as quote characters so the forum doesn’t convert any to smart quotes. You should add the parameters even though they are empty so Panorama knows what value belongs where. Don’t enclose the aggregate( function in parenthesis if you are using it alone.
Your code samples are not working because of a misplaced closing bracket: You had set the closing bracket immediately after the first parameter.
Gary posted corrected code already.
Working great now.
Will try tomorrow with the date constraints.
Thank you, Gary & KJM.
Good afternoon,
This is working well:
pattern(
aggregate({Amount},{},{yearvalue(Date)=val(2020)},{Expenses})
/
aggregate({Time},{},{yearvalue(Date)=val(2020)},{Trips}),
“$#,”)
It seems to be working regardless of whether I have any particular records selected in either of the databases, but it does not work if I summarize them. Is this correct?
Could the above formula be tweaked to make the calculation for just selected records, for example the month of January for both Expenses and Trips?
If not, how can the formula be changed to achieve that calculation?
Thanks.
In your code you have a function yearvalue(.
What about checking the documentation whether a function monthvalue( exists?
Understood.
The aggregate function works on the selected records, and the query parameter further limits the records that are aggregated. If you want to include all records in the aggregation regardless of the state of the selection in the data sheet, set the fifth parameter to true.
Well, it is just a matter of adding more conditions to your query formula as Kurt suggests. You can check for the two conditions you mention: no summary records and only records for a selected month. For this discussion let’s assume you have the desired month in the variable named theMonth. You can use the monthvalue( function to narrow it down to that month and you can use the info(“summary”) function to include only data records (no summary records). Here is the formula with these included. Note I have not checked this except for syntax.
pattern(aggregate({Amount},{},{yearvalue(Date)=val(2020) and monthvalue(Date)=theMonth and info("summary")=0},{Expenses})
/
aggregate({Time},{},{yearvalue(Date)=val(2020) and monthvalue(Date)=theMonth and info("summary")=0},{Trips}),
“$#,”)
That’s what I get for not checking the documentation before making a post. I am eliminating the info(“visible”) part of the formulas above. Thanks for pointing that out Tom.
Gary and CooperT,
Thank you for your clear explanations.
When I run this
pattern(aggregate({Amount},{},{yearvalue(Date)=val(2020) and monthvalue(Date)=02 and info(‘‘summary’‘)=0},{Expenses})
/
aggregate({Time},{},{yearvalue(Date)=val(2020) and monthvalue(Date)=02 and info(‘‘summary’‘)=0},{Trips}),
‘‘$#,’‘)
I get “info( function does not have a [‘SUMMARY] option” .
Again, make sure all the smart quotes are changed to straight quotes. There is definitely an info(“summary”) function. You could even try changing it to info({summary}) just to eliminate all smart quotes. If you intend to do a fair amount of coding I would suggest you turn off smart quotes in the Keyboard/Text panel of your System Preferences if it is turned on.
Gary,
I had run the formula through Word and switched all smart quotes to non-smart quotes. Obviously, something did not work well. The curly braces have fixed the problem.
This was the last pending issue I had. I only need this database for a specific project, and coding is not something that is a normal part of my activities.
I am much indebted to you and others in this forum because your help has allowed me to achieve what I needed.
JC, your code samples are showing that you are not using the usual straight double quotes. Instead you are obviously using two apostrophes.
While (single) apostrophes might be valid quotes for delimiting a text string, you cannot use them in Panorama X functions like
info("summary")
where the syntax requires double quotes.
That is why you got the error message about the missing ['Summary] option.
The explanation is appreciated.
Gary got it right with the answer in this case being smart quotes. If you use the Command+ option (to zoom in or magnify your screen’s resolution) on JC’s code, your see the closing quote on his Info(“Summay”) text has 2 opposing quotes (going in the opposite direction.) This is a result of the first one being ‘smarted’ and the second one not. Zooming in on his text is enlightening.
That’s the way how I discovered the apostrophes. The forum always tries to smarten the quotes, but ‘’ does not happen with the double straight quote chr(34).