Inline tables and combining count and sum..

[#1]

Hello all,

Some great karma on offer to anyone that can solve this for me...

An external developer has created some code for our QV database which uses the following in the Script..

Load * Inline
[Select4,Equ7,Equ8
NCO Enquiries,Count(,"IF(referredby = 101030294,referraldate))"
]

and this in an Expression of a Chart...(basically where Week ClassNum = 1 , count the above enquiry criteria)

$(=Equ7) {<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>} $(=Equ8)

to basically count the number of enquiries recieved in the latest week. I now have another field ENQ which needs to be simply totalled (The date function is already working)

Something like the below

Load * Inline
[Select4,Equ7,Equ8
NCO Enquiries,Count(,"IF(referredby = 101030294,referraldate))"(+SUM(,ENQ))

Any ideas please?

Thanks,

Matt

[stephencredmond]

Hi Matt,

Probably just need to extend the model a bit:

Load * Inline

[Select4,Equ7,Equ8,Equ7B,Equ8B

NCO Enquiries,Count(,"IF(referredby = 101030294,referraldate))",SUM(,ENQ)

];

Then you can include in the expression:

$(=Equ7) {<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>} $(=Equ8) + $(=Equ7B) {<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>} $(=Equ8B)

Regards,

Stephen

[#3 · Accepted solution]

Thanks Stephen,

I believe I have already managed to get around it by adapting existing code in the script:

Load * Inline
[Select4,Equ7
NCO Enquiries,"(Count({}IF(referredby = '101030294',(referraldate)))+Sum({}ENQ))"
];

and in the expression:

=$(=replace(Equ7,'{}','{<Nice_Week=,Week_ClassNum={$(=min(Week_ClassNum))}>}'))

But I will save your solution as well for future development as I can see that will be very useful for other areas.

Thanks again.