Hi all,
Unsure if it's just me, but I can't seem to find a way around this; apparently Jet is not very fond of array calculations (hitting shift-ctrl-enter, formulas with curly braces). Problem is as follows, excel's countif etc. don't work with more than one condition, so array formulas need to be used (i.e. {=SUM(IF(A1:A2=0;IF(B1:B2="Invoice";1;0);0))} [curly brackets done by excel]). Now, this works fine when going to report mode, but when switching back to design mode all curly braces are removed and excel no longer understands this to be an array function but a malformed general function.
Question: is there a way around this that i haven't thought of yet or are array functions somehow not supported by jet?
Best,
Chris
Date
Votes
9 comments
-
Jet Reports Historic Posts I have to say I'm not very familiar with the use of { and } in Excel. I'm not sure if I'm understanding the reason for the { and }. Can't the forumula just be:
=SUM(IF(B2:B3=0,IF(C2:C3="Invoice",1,0),0))
i.e. using "," instead of ";" ?
Do you have an example sheet you can send? -
Jet Reports Historic Posts I have to say I'm not very familiar with the use of { and } in Excel. I'm not sure if I'm understanding the reason for the { and }. Can't the forumula just be:
=SUM(IF(B2:B3=0,IF(C2:C3="Invoice",1,0),0))
i.e. using "," instead of ";" ?
Do you have an example sheet you can send?
The semicolon is actually because I'm using a localized version of Excel where commas are replaced by semicolons - I translated the function names but neglected to replace the semicolons for commas, sorry…
I'll try to attach my sheet here - What you're looking for is in the 'Cijfers' sheet, top section. Those blocks are populated by sum(if .. if .. ) from the NL below (using named range). The { } means that excel will process all newly created rows (since the named range auto-expands).
PS: If not all functions auto-translate, SOM = SUM, ALS = IF and the semicolon replaces the comma -
Jet Reports Historic Posts Ok… I have a new appreciation for those who have to work with English when its not their first language.
I wasn't able to run the report, mainly because I couldn't figure out what some of the words in the functions were (if my suggestion below doesn't help, feel free to translate: Eerste & Rijen) Would I be correct in Rijen being Rows?
This is what I would try If I could get it to compile. I would put the NL functions at the top of that sheet, and put your section with if statements below it. If that doesn't work, I would use cell reference ranges instead of the labels, i.e. E18:E19
Failing that I would calculate the top section (current top section) directly using jet, replacing the 0-30 at the top with an actual date range that would apply to. You would be able to drag that formula across all the cells in that matrix. If I can get a translation I could send you something to get you started. -
Jet Reports Historic Posts Wether you call them curly brackets, geschweifte Klammern or accolades… never seen them before in a formula like this, so I'm afraid I can't help you there.
But how about using the NP("Eval"…) function. My guess is that the "formula interpreter" then ignores your formula as it only looks at the NP.
Then it would be =NP("Eval";"{=SUM(IF(A1:A2=0;IF(B1:B2="Invoice";1;0);0))}"). Could you please let us know if this trick works as I can unfortunately not try it out.
(in Dutch it would become =NP("Steluit";…)
And for all of you who are in the mood of a small course "How do I write my Jet Reports commands in other languages" 8-) :
- click on the Windows start button;
- look for the Jet Reports folder;
- open the Tag Translation Reference file.
Cool thing, don't you agree? And so helpful if you need to work on international reports. -
Jet Reports Historic Posts As Hans pointed out, for language reference:
http://community.jetreports.com/viewtopic.php?f=7&t=163
SchoutenCC, is it also possible to put an introduction here?:
http://community.jetreports.com/viewforum.php?f=18
Leuk om te zien dat hier zoveel Nederlanstaligen zijn! :D
Mvg,
Jan -
Jet Reports Historic Posts I wasn't able to run the report, mainly because I couldn't figure out what some of the words in the functions were (if my suggestion below doesn't help, feel free to translate: Eerste & Rijen) Would I be correct in Rijen being Rows?
You are correct: Rijen = Rows, Eerste = FirstThis is what I would try If I could get it to compile. I would put the NL functions at the top of that sheet, and put your section with if statements below it.
The position on the sheet didn't matter for me as it even failed when the NL statement got a sheet of its own (which should be as interference-free as possible IMHO)If that doesn't work, I would use cell reference ranges instead of the labels, i.e. E18:E19
I actually only first tried labels after not getting it to work with direct cell references…Failing that I would calculate the top section (current top section) directly using jet, replacing the 0-30 at the top with an actual date range that would apply to. You would be able to drag that formula across all the cells in that matrix. If I can get a translation I could send you something to get you started
Do you mean having a separate NL function where each has a distinct datefilter (i.e. today-30..today)? Have considered, but wanted to keep the number of Jet calls to a minimum for speed reasons (some reports over here run for a good two hours)Wether you call them curly brackets, geschweifte Klammern or accolades… never seen them before in a formula like this, so I'm afraid I can't help you there.
Long story short: the accolades are not typed in by the user but filled in by excel internally. They are best read verbally as 'for each row in this matrix do xyz'. Try it out by not hitting enter after entering a formula but hitting ctrl-shift-enterBut how about using the NP("Eval"…) function. My guess is that the "formula interpreter" then ignores your formula as it only looks at the NP.
Then it would be =NP("Eval";"{=SUM(IF(A1:A2=0;IF(B1:B2="Invoice";1;0);0))}"). Could you please let us know if this trick works as I can unfortunately not try it out.
(in Dutch it would become =NP("Steluit";…)
Will definitely try it, though I've got some doubts apriori as the braces are not to be filled in literally by the user (the problem is not excel ignoring the matrix but Jet physically removing the braces when going back to design mode). -
Jet Reports Historic Posts SchoutenCC, is it also possible to put an introduction here?:
Leuk om te zien dat hier zoveel Nederlanstaligen zijn! :D
Will try to this afternoon ;) -
Jet Reports Historic Posts Issue has been resolved more or less - by copy/pasting to a brand new workbook and isolating all matrix calculations on a separate sheet, removing all named references within these calculations it worked (by some fluke as it would not work this way in the old workbook). This would seem to indicate an incompatibility, but on the third sheet there's still a matrix calculation and there are named references while it does work there *weird*.
Anyway, problem shoved aside. -
Jet Reports Historic Posts I know you said that problem has been solved but I thought this might help for future reference. Have you ever tried working with SumProduct Function in Excel; it'll do the same trick but without the Array Formula. For example:
Your Equation: {=SUM(IF(A1:A2=0;IF(B1:B2="Invoice";1;0);0))}
Becomes: =SumProduct((A1:A2=0)*(B1:B2="Invoice")*(1)) - If you ever want to sum instead of count just change the 1 to the array which holds the values to sum.
Please sign in to leave a comment.