david-h
Cloud Apps
Joined 10-25-2021
User Profile
User Widgets
Contributions
Re: OR filter inside a formula
Hi sharonhaviv​ , commas do act as an AND and I am to sure there is a syntax to change it as an OR. It is possible that some undocumented function exists though, but there are some solutions that you can use. There is one way of doing this through a complex JAQL custom filter replacing all of the "commas condition". The filter will be on INCIDENT_ID (same one os in the RSUM) and will use OR conditions. Another way is to add the RSUM. I think the logic will be the same : RSUM([# of unique INCIDENT_ID],cond1,cond2, cond3, ...) with commas = OR is equivalent to "RSUM([# of unique INCIDENT_ID],cond1) + RSUM([# of unique INCIDENT_ID],cond2) + RSUM([# of unique INCIDENT_ID],Cond3), ...) and in order to simplify the syntax, you can add to your data model some calculated fields that concatenate some of your conditions. For instance the 4 activity type. You separate the four fields by a string separator (, or ; or | or whatever) and then you condition can be different "contains" combine by OR. hope this helps. Best, David.18Views1like0CommentsRe: Days between x and today
Hi hlister​ , You are probably attempting to do it in the dashboard, and the error is likely to be "The DDIFF function must be wrapped" in an aggregation function. The DDIFF function is not an aggregation, so it calculates the time difference on each row of the underlying response of the generated query. As a result, to be valid, you must indicate an aggregation. So if you are to write something like "DDIFF(Requisition_Create , Now())" you can use MIN, MAX or AVG with the same result if your dimension is the requisition id. If you are using a more aggregative dimension, like team id, country, site or something like that, you can use MAX to have the longest time span, or MIN to have the most recent requisition. Let me know if this helps. Best, David.202Views1like0CommentsRe: Calendar in Sisense
Hi liamcameron , your issue seems more relatedto your data model but to be honest I am not sure I understood exactly what you are looking to achieve. Can you describe more precisely what you expect to see, maybe with a mockup done in excel or on paper +a short description of your data. In some cases, a pivot will be able to display categories, but in a multi fact model it is likely not to show any result if you don't add KPI's. For table I don't understand what is tour issue. Last question : which version of Sisense are you using ? Best, David550Views0likes0CommentsRe: Creating a Year-Over-Year Chart in Sisense
Hi alexism This is also an approach we us in many (many !) projects ! The dim date can work in different flavors (for instance for the use case Vadim is writing about, I usually work with a "month label" instead of shifting dates to current year), but it usually needs some slight work on the date itself, hence selecting a dim date or working on selected date. If you ant something more generic, that will work with any date, you can try the pastyear functions, or PREV function if you need more that tone year against the previous one. Hope this helps ! Best, David.2.5KViews0likes0CommentsRe: Running Rate
Thanks DRay to tag me ! MikeG , you can see each row as an individual calculation. For each of them, the engine applies a filter on DE_LENGTH. So the cells on first row are calculated with DE_LENGTH=0, second row DE_LENGTH=1, and so on. In order to have a calculation not being influenced by this filter (Power BI would call it context) you need to add a filter to your measure : , all([DE_Length]) ) So in your pivot : [Running Cures] / ([Total Placements] , all ([DE_Length]) ) Be careful, if you use explicitly SUM, it would be : [Running Cures] / (SUM ([Placements]) , all ([DE_Length]) ) Hope this helps. Best,David.2.5KViews1like1CommentRe: Running Rate
Hi MikeG, Not sure how you do it in Looker, or what your tries look like in Sisense. But it looks like you have the right method. The complete formula should looks like : RSUM([Total of Number of accounts cured]) / ( [Total of Number of accounts cured] , all([DE_Length]) ) Best, David.2.4KViews1like0CommentsRe: Sum for new date
Hi Avery, For your use case I think you should do the calculation in the datamodel. It seems to be more a process calculation, and you will be able to visualize it in the dashboards. You can add a calculated column, DueDate, defined as adddays([RegisteredDate], 21) Best, David.3.1KViews2likes0CommentsRe: Sum for new date
Hi, It depends on what you want to achieve. If you need to display a date + or - X days, it does not exist out of the box in Sisense. You can always use a Javascript script in your widget to do this. If you want to get the value of a KPI for date + or - X days, you can use the time windows functions : pastyear/pastmonth/pastweek/pastday or next and prev that can be used to get the KPI value at a relative date. For example, the values of Revenue 21 days ago you can use : ([Total Revenue],prev([Days in Date],21)). This will give the Total Revenue 21 days before the date in the current context. Best, David3.1KViews1like0CommentsRe: SUM based on Text Criteria
Hi Erick, not sure I understand exactly the use case you are on, but it seems that you could use multi pass agregation (kind of adding a group by in an intermediate calculation in your measure). You may also use this old IT trick : yes=1 and no=0. Let us know if this can be useful in your use case, and if not, maybe you can share a siplified data model. Best, David.2.3KViews1like1CommentRe: Dimension filter on id not working correctly
Hi Sam, In a multi-fact table data model, there are a few guidelines to follow to be sure the query generation will be as expected. Main ones are : 1) to link all fact tables to all dimension tables 2) hide (or make invisible) in the fact tables the key that is used in the join to the dimension => this is to enforce the use of dimension tables when filtering, and not the fact table. If you use the fact table to filter, then the risk is to generate many to many relationship as the user does not have direct control over the query generation process. In any case, if you keep both keys visible in the date model, the user will have both field available when designing widgets, which will lead to confusion. One of the limitation of this principle is when you want to count on an id. For instance if you want to count products or clients, you want to do "unique count of productId". But you have to do that from the fact table, and not the dim table. In this case, you would duplicate the productID field from the relevant fact tables, and likely renamed it with some meanings (ex. : OrderedProductId, InvoicedProductId, ...), explicating from which fact table the Id comes from. Let me know if you want more details. Happy to jump in a call if you want. Best, David.2.4KViews0likes0Comments