Group by

[#1]

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.

[sunny_talwar · Accepted solution]

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.

[#3]

Hi there Sunny T,

Thanks for your comment. this should do the trick.

[oknotsen]

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 .

[#5]

Hello Onno,

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