Multipass Aggregation Failing

[mamuewue]

Hi community,

I have a multipass function which isn't doing what I expect from it. 

In an widget I want to report how many SMS have been send, and if the number is above 5 per "sim_id", the to-be-credited SMS shall be 5. If the number of SMS sent is below 5, the actual amount of SMS shall be credited.

I report on the "to-be-credited SMS" field with the following multipass expression:

Sum([sim_id],
   CASE
    	WHEN [Total sms_volume]>5 THEN 5
    ELSE
    	[Total sms_volume]
    END
   )

When I keep "sim_id" in the rows, the formular does in the total field what it should. The to-be-credited SMS number of 13,399 is correct. (Certainly there are much more rows and pages).

The challenge is, i want the "sms_id" row not to appear, and still see the correct 13,399 value. 

However, when i remove the "sms_id" row, i get the number 5 as result. As per below screenshot.

What am I doing wrong?

Thanks, MaMueWue

[Ido_QBeeQ]

Hey @mamuewue 

I believe you'll need to add the [Org Name] dimension into the multipass aggregation formula right after the sim_id separated with a comma:

Sum([sim_id],[org name],
   CASE
    	WHEN [Total sms_volume]>5 THEN 5
    ELSE
    	[Total sms_volume]
    END
   )

LMK if this does the trick,

Feel free to reach out for more questions,

Always here to help, 

Ido Darnell 

QBeeQ - Gold Implementation and Development Partner

[mamuewue]

Hi Ido,

thanks for offering to help.

It seems i can't just add another dimension. Sisense spids the error as shown in the screenshot?

Thanks, Mathias

[Ido_QBeeQ]

@mamuewue you may need to add another set of parenthesis to encapsulate the CASE/WHEN

If this doesn't work fee free to reach out 

[mamuewue]

HI @Ido_QBeeQ , sorry for bugging you, but I am not figuring it out. Can you have another look. This is what I tried:

a) --> Works as a formular but throws an error while calculating it 

 --> 

b) Doesn't allow me to "save" the formular with the same error as above "Function SUM accepts 1 or 2 parameters".

Thanks all...

[Ido_QBeeQ]

Hey @mamuewue sorry I think I misread, your formula is correct, I think you're just missing paranthesis around the CASE/WHEN. 

Sum([sim_id],
   (
   CASE
    	WHEN [Total sms_volume]>5 THEN 5
    ELSE
    	[Total sms_volume]
    END
    )
  )

If that doesn;t work, try saving the CASE/WHEN as an indepandant formula, then adding that formnula into the multipass.

LMK how it goes!

Reach out if not!

We're always here to help 🙂

Sisense #1 Implementation and Solution Development Partners

[mamuewue]

Hi @Ido_QBeeQ , I really appreciate your help, but both "options" (adding parenthesis, using it in a formular) don't work still. Not sure what it is. Let's give up on it. Thanks again, i really appreciate you are working it with me.

[Ido_QBeeQ]

@mamuewue 

let's hop on a quick call to troubleshoot, feel free to reach out

[DRay]

Hello @mamuewue. Were you able to get this resolved?