Forum Discussion

mamuewue's avatar
mamuewue
Cloud Apps
12-22-2023

Multipass Aggregation Failing

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

dashboards & reporting

8 Replies

Replies have been turned off for this discussion
  • Ido_QBeeQ's avatar
    Ido_QBeeQ
    ETL
    01-09-2024

    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's avatar
      mamuewue
      Cloud Apps
      01-09-2024

      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's avatar
    Ido_QBeeQ
    ETL
    01-03-2024

    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's avatar
      mamuewue
      Cloud Apps
      01-08-2024

      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's avatar
    Ido_QBeeQ
    ETL
    01-08-2024

    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's avatar
      mamuewue
      Cloud Apps
      01-09-2024

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