cancel
Showing results for 
Search instead for 
Did you mean: 

Multipass Aggregation Failing

mamuewue
8 - Cloud Apps
8 - Cloud Apps

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).

mamuewue_1-1703274574387.png

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.

mamuewue_0-1703274391244.png

What am I doing wrong?

Thanks, MaMueWue

8 REPLIES 8

Ido_QBeeQ
9 - Travel Pro
9 - Travel Pro

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_QBeeQ_0-1704271805699.png

Ido Darnell 

QBeeQ - Gold Implementation and Development Partner

 

 

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?

mamuewue_0-1704700602695.png

Thanks, Mathias

Ido_QBeeQ
9 - Travel Pro
9 - Travel Pro

@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 

 

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 

mamuewue_0-1704811168040.png --> 

mamuewue_1-1704811258971.png

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

mamuewue_2-1704811373312.png

 

 

Thanks all...

 

 

 

 

Ido_QBeeQ
9 - Travel Pro
9 - Travel Pro

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

 

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
9 - Travel Pro
9 - Travel Pro

@mamuewue 

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

DRay
Community Team Leader
Community Team Leader

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

David Raynor (DRay)