Difference between revisions of "Talk:Documentation/How Tos/Calc: Database functions"

From Apache OpenOffice Wiki
Jump to: navigation, search
(New page: My question is : How to filter a database in Calc to include rows between two dates, where an earleir date than today is already defined in the spreadsheet? TIA Nicolas Beaudet)
 
(→‎Putting parameters in criteria)
 
(One intermediate revision by the same user not shown)
Line 6: Line 6:
  
 
Nicolas Beaudet
 
Nicolas Beaudet
 +
 +
== Putting parameters in criteria ==
 +
 +
The way to have variable criteria is to put calculated expressions in the criteria range. For example:
 +
 +
{| class="wikitable"
 +
|-
 +
!
 +
! A
 +
! B
 +
! C
 +
|-
 +
| 1
 +
| ColA
 +
|                 
 +
|
 +
|-
 +
| 2
 +
| = "<" & C2
 +
|
 +
| 42
 +
|}
 +
 +
The criteria range is A1:A2, selecting the rows for which the value in column "ColA" is less than the value in C2 (42 in this case).
 +
 +
So if the database rows are:
 +
 +
{| class="wikitable"
 +
|-
 +
!
 +
! X
 +
! Y
 +
! Z
 +
|-
 +
| 12
 +
| ColA
 +
| ColB               
 +
| ColC
 +
|-
 +
| 13
 +
| 41
 +
| 100
 +
| 2
 +
|-
 +
| 14
 +
| 43
 +
| 1
 +
| 200
 +
|}
 +
 +
then the expression:  =DSUM(X12:Z14; "ColC"; A1:A2)  will calculate the value 2.
 +
 +
If the value of cell C2 (the parameter value for the criteria) is changed to 44, the result will be 202.
 +
 +
I'm not sure if the description proposed on this page shouldn't be enhanced to explain this.
 +
 +
--[[User:Ztevepowell|Zteve]] 15:59, 7 January 2009 (CET)

Latest revision as of 14:59, 7 January 2009

My question is :

How to filter a database in Calc to include rows between two dates, where an earleir date than today is already defined in the spreadsheet?

TIA

Nicolas Beaudet

Putting parameters in criteria

The way to have variable criteria is to put calculated expressions in the criteria range. For example:

A B C
1 ColA
2 = "<" & C2 42

The criteria range is A1:A2, selecting the rows for which the value in column "ColA" is less than the value in C2 (42 in this case).

So if the database rows are:

X Y Z
12 ColA ColB ColC
13 41 100 2
14 43 1 200

then the expression: =DSUM(X12:Z14; "ColC"; A1:A2) will calculate the value 2.

If the value of cell C2 (the parameter value for the criteria) is changed to 44, the result will be 202.

I'm not sure if the description proposed on this page shouldn't be enhanced to explain this.

--Zteve 15:59, 7 January 2009 (CET)

Retrieved from "https://wiki.openoffice.org/w/index.php?title=Talk:Documentation/How_Tos/Calc:_Database_functions&oldid=106561"
Personal tools