topic Re: Problem with aggr() or only() in QlikView

Problem with aggr() or only()

ericheleine — Mon, 22 May 2017 11:04:33 GMT

Hi every body,

My goal is to calculate the total cost only with bank where i have an interaction

I don't arrive to fix this issue with the function aggr

Total cost expected is 40000

thanks for your help

tresB — Mon, 22 May 2017 11:08:07 GMT

Try something like:

Sum( {<[Interaction per month]={'>0'}>} Cost)

sunny_talwar — Mon, 22 May 2017 11:14:36 GMT

Or may be this if you don't have a pre-aggregated Interaction per month column

Sum({<Bank = {"=Count(Interaction) > 0"}>} Cost)

ericheleine — Mon, 22 May 2017 11:15:35 GMT

thank you Tresesco for your quick answer.

that doesn't work because Interaction per month is a formula:
=count(interaction)

The set analysis doesn't work in this case

eric

tresB — Mon, 22 May 2017 11:17:18 GMT

Then Sunny's suggestion below should work.

ericheleine — Mon, 22 May 2017 11:18:41 GMT

thank sunny

works perfectly

regards

sunny_talwar — Mon, 22 May 2017 11:18:54 GMT

Do you have just Bank as dimension or do you have Bank and Month as dimension? If you just have Bank as dimension, then you can try this

Sum({<Bank = {"=Count(Interaction) > 0"}>} Cost)

If you have Bank and Month as dimension, then may be create a new field in the script like this

LOAD Bank,

Month,

Bank&Month as Key

....

FROM ....;

and then this

Sum({<Key = {"=Count(Interaction) > 0"}>} Cost)