Forum Discussion

oliviar's avatar
oliviar
Data Storage
05-10-2022
Solved

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

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 d...
Finance Analytics
  • HamzaJ's avatar
    HamzaJ
    05-10-2022

    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

Ophir_Buchman's avatar
Ophir_Buchman
Data Integration
05-16-2022

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