How 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?Solved12KViews0likes2Comments
Remove Widget Title Box
Hello. Is there any way to remove the title portion of a widget? I often don't place a title on indicator widgets, but the title box still limits how small the widget can be and leads to some awkward white-space. Is there any way to get rid of the box and the white-space that comes with it?Solved7.1KViews2likes2Comments
Using answer to a calculation within another calculation
Im looking to use the answer from one calculation in another calculation on a separate widget with different filter set up. This is my first calculation, it counts how many sales we have prior to drafting for each model and divides by the total number of sales. This then shows the percent sold prior to drafting for each of our models. (count([UniqueJob]), [SoldPriorToDrafting], [Years in FinalSaleDate]) / (count([UniqueJob]), [Years in FinalSaleDate]) The problem is I don't want the calculation to run first on the new widget I just need to use the number from the pivot table above as the other widget shows each individual job within the model. The calculation doesnt work when looking at it in this view.6.5KViews0likes4Comments
Building a pivot table with TY vs LY with % growth
In a pivot table where I have the year 2021 data showing, how can I show the previous years sales (depending on which year has been selected from the main dashboard) including a % growth in the following columns in the pivot?5.7KViews0likes4CommentsHow 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.6KViews0likes3CommentsOverlapping 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.1KViews0likes5CommentsEdit Widget Script Sisense to display NA for specific condition
I want to add a custom script for my widget so that whenever user selects date range spanning more than 1 year, the widget (gauge indicator) should display NA for both value and secondary measure. Any help will be appreciated.Solved4.6KViews0likes6Comments
Widget to display time
Hi, We have a requirement where we have to display the local timezone of the country that is being selected from the dashboard filter. The dashboard filter on country has values like Italy, France, Netherlands, UK, US etc. When the user selects "Italy" in the filter, i want the widget to display the value of Italy's local time, irrespective of the timezone of the user. The same widget should display France's local time when the user selects "France" from the filter. Do we have an out of box/ built-in widget to display time (like a clock widget).3.3KViews0likes6Comments
Count days between Open Date and Closed Date, but use today's date if there is no Closed Date
Hello! I'm trying to create a formula that will calculate the number of days between two dates. The first date would always be an "Open Date", which all items should have. The second date would be conditional: I want it to use a "Closed Date", or if there isn't a Closed Date (i.e. it's null), then use today's date. I tried using this formula: IF(ISNULL([Days in Dispute Closed On]), DDiff(Now([Days in Dispute Opened On]),[Days in Dispute Opened On]), DDiff([Days in Dispute Closed On],[Days in Dispute Opened On])) Basically: if Closed On is null, calculate difference between Today and Opened On, otherwise calculate difference between Closed On and Opened On. Seems pretty straightforward, but Sisense won't accept this. It says "Error in function definition (IsNull): Expecting parameter of type 'Numeric Expression' but found 'Set'." I tried changing the ISNULL so it was looking at a count instead of an actual date: IF(ISNULL([# of unique Days in Dispute Closed On]), DDiff(Now([Days in Dispute Opened On]),[Days in Dispute Opened On]), DDiff([Days in Dispute Closed On],[Days in Dispute Opened On])) But that just changes it to a different error: "Error in function definition (IF) in 'numeric expression 1 (true)': Expecting parameter of type 'Member Expression' but found 'Dimension Expression'." This doesn't make any sense, because if I just do this by itself: DDiff(Now([Days in Dispute Opened On]),[Days in Dispute Opened On]) it works just fine?? So why wouldn't it work as part of the IF statement? What am I doing wrong? How can I accomplish the calculation that I want?Solved3KViews0likes1Comment
