Difference between revisions of "Talk:Documentation/How Tos/Calc: Database functions"
(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) |
Ztevepowell (Talk | contribs) (→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)