How do I sum if or count if using conditional values?

[oliviar]

Hi Community,

I'm trying to figure out why my pivot table is returning incorrect values, and how to fix it.

I need a variety of formulas that apply to each program in my pivot table to collect data about how much money we are contracted to receive, how much money we have collected to date of the report exported, how many students have only paid their deposit and what amount that totals, as well as how many students are considered Confirmed, and how many are on Scholarship. Here's a snapshot of what this long pivot table looks like:

Committed Revenue should be =sum(total orders) but it's inflating the value from what is in the data. When I click into the formula it looks like this, which is why I think it's inflating, but I can't figure out how to get it to read the data correctly.

Total Paid to date is having a similar issue. It should be =sum(Total Payments) and it looks like this in the formula field, containing all items instead of sum:

Deposit Paid Only (Dollar Amount) is a bit more interesting but I still can't get it to look right. It should be =sum(Total Payments) when total payment = 50. I have the filter set to =50 but it is not returning the correct value.

Deposit Paid Only (Number of Users) is similar to the deposit paid only dollar amount, and should =count(Applicant ID) when Total Payments = 50. When I add Total Payments to this formula, the filter on Total Payments goes away, so I can't get it to only count when Total Payments = 50.

I have a couple of fields in this pivot table that are calculating mostly correctly, are able to use filters, and they are set up like this:

to count the number of students (applicant IDs) in the program when confirmation status = confirmed

to count the number of students (applicant IDs) in the program when confirmation status = confirmed and payment result = scholarship

I need a few basic formulas that I can then change to fit the needs of each field, but everything I've read online isn't making sense. I know I may need a CASE statement but I've tried that a couple times and still no luck.

Please help!

[HamzaJ · Accepted solution]

Hi @oliviar 

A sum of a total (SUM(TOTAL)) should not mess up your sum (atleast not in my sisense deployment). If you think the numbers are to high, it could indicate that there is a many to many happening. This extrapolates the data. Enable/download the visualise jaql from the marketplace to determine if this is the case. if so you can change it and the numbers will be correct.

Second; you cannot filter on a sum or on a value that is being summed / aggregated. To do a filtered measure (or sum in your case) try the following (SUM(TOTAL_ORDER), [TOTAL ORDER]) . Filter on that last field and select 'All items' when selecting the field. Then the filter will work

Hope this helps

[oliviar]

Thank you so much! This worked beautifully. I really appreciate you taking the time to help!

[Ophir_Buchman]

Hi @oliviar 

The answer to your "inconsistency" question is simple, however, I agree the behavior isn't very intuitive:

• To display a pivot chart, the front-end browser sends numerous JAQL requests (queries) to Sisense. These include: Showing the RAW values, calculating "Sub Total" and "Grand Total" values, and more.
• In your example, you've requested to display a "Source" and a few measures related to it.
• You'll find that the way to calculate each measure requires performing JOIN operations between tables in the data model.
• Assume the following scenario:
  • You have a product table dimension table connected to a "sales" and a "purchases" fact tables
  • You would like to know both the sales and purchases for a specific product
  • Say that product "A" was purchased but never sold
• Result:
  • Sisense uses an INNER JOIN to join tables
  • As this join will filter out product "A" - It won't show in the pivot chart
  • As the "Grand Total" of sales and purchases is calculated independently - It will take product "A" into account
• Changing the behavior of the "Grand Total" is easy (click the ... next to the value and modify the "Subtotal By" value) - However, you'll have to check what's the right total value for you