Question:
Hi
Ok ill cut to the chase is this possible???
I have the following: -
START---------------------not in query
END------------------------not in query
DIVISION------------------in query
CONTRACT----------------not in query
KEY FIGURES--------------in query
DIVISION------------NUMBER OF DAYS---------------NUMBER OF RECORDS
gas-----------------------11,258,100---------------------------157
electricity----------------9,100,123-----------------------------93
Ok the number of days is the total number of days difference for all the records 'start' and 'end' differences as they are not in the query breakdown.
This could be a mixture of lengths (e.g. 200, 12000, 357, 4500)
However, what i want to do is just show the ones with lets say difference over 4000 (maybe a condition) BUT still not put in the 'start' and 'end' date in the query!!!! as this takes up too many query lines.
I have tried hiding them etc but it still shows all the individual sub totals.
Any help would be very much appreciated.
BW Version 3.1
BW Bill
Answer:
do you mean end minus start for each contract ? or are start and end 2 different KF of a same record ?
_________________
When my car stops, I first look at the gasoline's level before dismounting the engine.
Tuly Idiot's fan club active member.
Answer:
If all Chars were ina query it would look like this:
DIV----CONT------START-------------END------No OF DAYS---NO OF RECS
gas------1---------01/01/03--------01/02/03-----------30--------------1
----------2---------01/02/03--------15/02/03------------45--------------1
SUBTOTAL-----------------------------------------74------------2
electric--3---------01/01/03--------01/03/03------------60--------------1
SUBTOTAL-----------------------------------------60------------1
TOTAL---------------------------------------------134-----------3
But idont want either 'contract' , 'start' or 'end' in the query but still be part of the calculations.
The NUMBER OF RECS is counting the values in terms of instance of contract so this is no problem but its the start and end date that is the issue.
Hope this helps
Answer:
ok 30 + 45 is 75 ...sorry
Answer:
hmmm... don't see how this could be possible with the Query Designer... it is possible with VB however
cheers,
El Belgio
Answer:
To be very clear ....
If you choose to only see contracts which are longer than 40 (for example), you want to see in your report :
Gas 45 1
Electric 60 1
To do this, you've to create calculated key figures before aggreggation (assuming that you've only 1 record per contract in your infoprovider, if not, need deeper analysis)
Create a calculated KF which formula is :
(Number of days) * (number of days > 40)
You can of course replace 40 by a formula variable ...
This will be equal to number of days only if number of days is greater than 40, 0 else.
Same thing for the number of records, but easier, just remove the first number of days.
_________________
When my car stops, I first look at the gasoline's level before dismounting the engine.
Tuly Idiot's fan club active member.
Answer:
Thnks for the suggestion
I think im missing something small
I did the calculation
(Number of days) * (number of days > 40)
However, when doing this the enhance part that would allow me to select 'before aggregation' was greyed out so i couldnt do this.
Could this be because Number of days is a CKF that minus one formula variable from another (e.g.: start and end). Number of Days would let me do the before aggregation.
When i did it (without before aggregation) it still gave the total as being the full amount of all contracts but if i added contract then it did show:
number of days is greater than 40, 0 else.
However i still needed the contract in to show this????
i just need
e.g;
DIVISION----NUMBER OF DAYS----------NUMBER OF RECORDS
GAS------------------6534----------------------------17
ELECTRIC------------2546----------------------------32
6534 and 2546 being the total of those just above 40!!
Answer:
Is number of days a calculated KF after aggregation ?
If yes, I guess you know the solution ..
Every calculated KF you use in calculating before agg. MUST be before aggregation !!
_________________
When my car stops, I first look at the gasoline's level before dismounting the engine.
Tuly Idiot's fan club active member.
Answer:
Fred
I changed the 'Number of Days' CKF to before aggregation
But then when creating the second one with the calculation you gave
(Number of days) * (number of days > 40) it will not allow me to do it before aggregation.
The actual calculated key figure does work but i have to put in Contract and then do a condition or filter to get rid of the ones that are 0.
I assume if it allowed before aggregation then it wouldnt show the ones that are 0.
Is there a reason why it wojt allow me to do the second CKF before aggregation
Thanks
BW Bill
Answer:
Is there a reason why it wojt allow me to do the second CKF before aggregation
are you using another CKF (or RKF) in it? if so, that would be the reason!
Answer:
Yes
Number of Days is a CKF so i suppose that this is a dead end for me i think i will just have to show the individual lines and use conditions and filters instead of just the total amount.!!
Thanks for all your help guys
BW Bill (Frazzled brain BW Bill)
Answer:
Just try to replace the calculated KF (number of days) by the formula defining it !
For example, instead of (Number of days ) * (number of days > 40), just enter
(end date - start date) * ((end date - start date) > 40),
or
( end date - start date ) * (end date > start date + 40) ......
If I well remember (but you know the problem we have, us old men, loosing our memory), I already had this problem in the past ...
Heu ... Excuse me, who are you, mister ?
_________________
When my car stops, I first look at the gasoline's level before dismounting the engine.
Tuly Idiot's fan club active member.
Answer:
Many Thanks Fred...I think i may have got it!!