topic Re: Group by in QlikView

Group by

Fri, 27 May 2016 07:26:00 GMT

Good morning,

Who can help me with this problem i have.

For the calculation of the wault i use the next expression;

avg({$<CAMRULE_HCHARGECODE = {4,5,23}>}

if(LEN(TRIM(TENANT_DTBREAK)) = 0 AND TENANT_DTBREAK > floor(now()) and CAMRULE_RENT_YEAR > 0,

(TENANT_DTBREAK - floor(now())) / 365.25 * CAMRULE_RENT_YEAR,

if(TENANT_DTEXPIRATION > floor(now()) and CAMRULE_RENT_YEAR > 0,

(TENANT_DTEXPIRATION - floor(now())) /365.25 * CAMRULE_RENT_YEAR)))

/ CAMRULE_RENT_YEAR

This calculation does exactly what i would expect it to do but it's per tenant and when i remove the dimension tenant to see the wault for the selected properties all results will be removed.

Re: Group by

sunny_talwar — Fri, 27 May 2016 15:47:48 GMT

May be this:

Sum(Aggr(

avg({$<CAMRULE_HCHARGECODE = {4,5,23}>}

if(LEN(TRIM(TENANT_DTBREAK)) = 0 AND TENANT_DTBREAK > floor(now()) and CAMRULE_RENT_YEAR > 0,

(TENANT_DTBREAK - floor(now())) / 365.25 * CAMRULE_RENT_YEAR,

if(TENANT_DTEXPIRATION > floor(now()) and CAMRULE_RENT_YEAR > 0,

(TENANT_DTEXPIRATION - floor(now())) /365.25 * CAMRULE_RENT_YEAR)))

/ CAMRULE_RENT_YEAR,

tenant))

Replace Sum with whatever outer aggregation makes sense for your case.

Re: Group by

Mon, 30 May 2016 08:53:47 GMT

Hi there Sunny T,

Thanks for your comment. this should do the trick.

Re: Group by

oknotsen — Mon, 30 May 2016 09:16:42 GMT

If your question is now answered, please flag the Correct Answer.

If not, please make clear what part of this topic you still need help with .

Re: Group by

Mon, 30 May 2016 09:19:37 GMT

Hello Onno,

Thank you for your comment, I've set the Correct Answer flag.