oliviar
Data Storage
Joined 4 years ago
User Profile
User Widgets
Contributions
Apply a Formula to Select Rows in a Pivot Table
I have a pivot table and I'm pretty happy with how it looks. However, I want to adjust the formula in two of the columns, if possible. The pivot table looks like this: There is a formula in 2 of the value columns: LTD Remaining to Goal, and Forecasted Remaining to Goal. I will only focus on one formula since I think the solution I'm searching for will be easily applied to both fields. The formula for the LTD Remaining to Goal field = sum(REVENUE) - 110,000 110,000 is the goal This formula applies to every Product Code in the Pivot Table. I want to see all the codes shown in the Pivot Table, but I only want the LTD Remaining to Goal formula to calculate for Product Codes that start with BK. I do not want the formula to calculate for any Product Code that starts with AB or ends in W. Is this possible? How would you do this?1.5KViews0likes1CommentRe: Center Pivot2 (Linux)
Hey harikm007 do you have a similar script that works for Windows? I am not sure what information you need about the system we are on and what's running (or how to check for that information) but I tried this script on my dashboard and it's not working. I have multiple widgets and text boxes on my dashboard. I just want to spruce it up and reduce some white space by centering the pivots that I have in their widget placement spaces. Can you help? Thanks!6.2KViews0likes0CommentsRe: Overlapping Column Chart
Thank you! I like the top one. I think this is what we are looking for. The only thing that's kind of bugging me now is when I hover over the product in the bar graph, the numbers associated with the goal and actual are not formatted the same way as they were before--they don't look as clean. I have tried to change the format in the dashboard design and it won't change. Do you have any pointers here? Ideally I would be able to see the comma separators, dollar signs, and reduce the decimal places so it is a rounded figure. Thanks!5.1KViews0likes2CommentsOverlapping Column Chart
I am looking at a Goal vs. Actual column chart. I have two context cases for this framework: units goal and units sold, and revenue goal and revenue collected. I have my bar chart working correctly, but I want to change the design of it a bit to be more visually appealing. I am not satisfied with the Classic design view. It looks like this: I have tried the Stacked and Stacked 100 views but they aren't doing what I need them to do. I want the column charts to overlap. If I have a product with a goal of 100 units, and we've sold 20 of them so far, the Stacked design view makes my entire bar equal 120 (combining the goal and the actual). I don't want that. And I don't want a static goal or budget line across my graph. I want the bar to stay at the goal of 100 and to see the 20 units sold in front of the 100 unit goal bar. Kind of like this, visually: Does anyone know how to do this? I think it should be a default design view option. Thanks for the help!Solved5.2KViews0likes5CommentsRe: How to Calculate YTD by Month in a pivot table
Hey there, Thanks for the feedback. I was aware of the RSUM quick functions, but they weren't working on the data cube my dashboard was connected to. I had to correct a few things. Even after my cube was fixed, the Quick Functions still weren't working. I found a solution that actually isn't documented anywhere on Sisense webpages or forums that I've come across so far, so I thought I'd share my custom function: =RPSUM([Total BUDGET],12) This formula takes the monthly budget for the year (as filtered by on my dashboard filter) and cumulatively adds it to the previous month's budget and cumulates from there. At first I only had =RPSUM([Total BUDGET]) and this was working, but only for 2 months at a time (Jan + Feb, reset for March + April, etc). By adding the 12 it knows to repeat after 12 periods, so it takes care of the entire year for me. I was able to apply the RPSUM with 12 periods to my Budget, Revenue, and Variance (Revenue - Budget) columns. It was a long walk but I got there! I'm grateful to know this function when the Quick Functions don't work.12KViews0likes0CommentsHow to Calculate YTD by Month in a pivot table
I have a pivot table that works for my team. It looks like this: The variance column is a simple formula showing the difference between Budget and Revenue (Revenue - Budget). I have received a request to expand this pivot (or create a new one based off of the data in this current one) that calculates the YTD for each of these fields. So, for example, I'm trying to build 3 new columns in the pivot table: March Budget YTD = January Budget + February Budget + March Budget March Revenue YTD = January Revenue + February Revenue + March Revenue March Variance YTD = March Revenue YTD - March Budget YTD I need these additional columns for each month, either added to this existing pivot table, or in a new one with a filter for the month the team wants to look at. Is this possible in a pivot table? I need to keep it split by Customer Type. How would I solve for this?Solved12KViews0likes2CommentsHow 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 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!Solved5.7KViews0likes3Comments