Best Of
Re: Magic ETL Tiles groups for clearer organization and visibility/observability :D :D :D
This is sick. Love the bit on your current process as well.
Re: Aggregation Issues with Beast Mode Calculation
Hello @Cara ,
Great question, this is due to the aggregation rollup on the card and the dimension grouping used on the card. The **** error indicates that the grouping in your pivot table doesnt match the grouping in another part of your visual, in this case its your Beast Mode code.
A common way this happens is when you reference a column in your Beast Mode code that is not used on the card itself. Here is a good example of how it happens:
This Sample Beast Mode code uses the Item Color column, but that column is not used in the card. The Beast Mode code creates Item Color groups, and the card doesn't know what to do with those group because they were not used on the card.
CASE
WHEN `Item Color` = 'Blue' THEN SUM(`Order Quantity`)
WHEN `Item Color` = 'Black' THEN SUM(`Order Quantity`)
WHEN `Item Color` = 'Red' THEN SUM(`Order Quantity`)
ELSE 0
END
In the below screen shot we can see the error due to the Item Color grouping in the BM code above.
When you add in the Item Color column, you can now see the why there were multiple groups, the Beast mode code has groups split out causing separate aggregations instead of a rollup aggregation.
To fix this issue, you want to correct your Beast Mode grouping or adjust your columns on the Pivot table to reflect the groups created in the Beast Mode.
Here is a good example of a corrected Beast Mode code that corrects the grouping & Pivot table.
SUM(CASE
WHEN Item Color
= 'BLUE' THEN Order Quantity
WHEN Item Color
= 'Black' THEN Order Quantity
WHEN Item Color
= 'Red' THEN Order Quantity
ELSE 0
END)
The results of the new Beast mode code aggregates the data correctly for the grouping rollup:
Re: Sharing custom themes in App Studio
Custom themes are currently saved to an individual app.
To create custom theme, a user needs to select save as theme then it will store it to custom themes panel. We are working to add the capability to copy a theme from one app to another in the upcoming months that would let you use custom themes across different apps.
Current workaround is to duplicate the app and save that theme and use that new app!
Re: Median Date
Another potential alternative is to convert your dates into unix timestamp (TO_TIMESTAMP) which would be a number, this would then allow you to run a median on the number and then just convert the median back to a date (FROM_UNIXTIME)
Re: Remove lookback period from the data and see the values.
@Prathmesh24_Diacto - I just completed this write up for a similar question. I think you'll find it useful. I've adjusted it to fit your use-case.
This answer also expands on the requested functionality, by adding in a toggle that will allow users to flip between limiting the data based on a selected date, and showing all data. As well as allows users to specify the number of days to calculate back from the selected date.
We can use variables to allow users to select a date that will then limit the data shown. In this case, we are being asked to set it up so that the previous 28 days of data is excluded based on the user selected date.
Step 1-1: Create a variable (SELECTED_DATE) that will be used as a control to let users select a date.
Step 1-2: Create another variable (USE_SELECTED_OR_ALL) that will be used as a control to let users toggle between selecting a date, and showing all data. Set up 2 options (Selected Date & All Dates) for this variable.
Step 2: Use the SELECTED_DATE and USE_SELECTED_OR_ALL variables in a case statement. This case statement will be used as a filter in Analyzer. I called this Beast Mode (RELEVANT_DATA_FILTER)
CASE WHEN USE_SELECTED_OR_ALL = 'Selected Date' THEN CASE WHEN date < = DATE_SUB(SELECTED_DATE,interval 28 day) THEN 'Include' ELSE 'Exclude' END ELSE 'Include' END
Step 3: Use RELEVANT_DATA_FILTER as a filter on the card in Analyzer. You will want to select the value "Include" for the filter.
Step 4: Put your card on a dashboard, and then load in the controls so that users can interact with them.
If a user selects "All Dates" the card will show data for all dates. The cool thing here is that users will still be able to use the date range controls on the dashboard to control how the data is displayed (by day, month), and what range is displayed (current month, YTD, etc).
If a user selects "Selected Date" they can then select the date from which they want to show the exclude the previous 4 weeks of data from. See how I selected 3/29/2024, and my the latest date of shown in my chart is March 1st (28 days before March 29th).
Note, you can change your variables default values. For example, you can set USE_SELECTED_OR_ALL to default to "All Dates" or "Selected Dates" depending on your use case.
You could take this even further, and create another variable (DAYS_TO_EVALUATE) that would let users dynamically change the number of days from the selected date that are shown. To do this, you would simply replace the hard-coded value of 28 in the case statement with your variable, and then include that variable on your dashboard as a control.
CASE WHEN USE_SELECTED_OR_ALL = 'Selected Date' THEN CASE WHEN date < = DATE_SUB(SELECTED_DATE,interval DAYS_TO_EVALUATE day) THEN 'Include' ELSE 'Exclude' END ELSE 'Include'END
Feel free to reach out directly via message with any questions 😁
If this answers your question, please remember to 'like' and 'accept' the post as the answer.
Re: Target a specific card value using ID or something.
Maybe I'm mis-reading the question, but it sounds like you would just need an alert. If you want to know when a summary number falls below a threshold, you can set up an alert on that card specifying that threshold: