Best Of
Re: Creating Card filters on values currently absent from a dataset
You could configure a beast mode to return the opposite and then do a NOT IN
CASE WHEN Target Go-Live Date` IS NULL THEN 'Blank' ELSE 'Not-Blank' END
Alternatively if you're wanting to do that exact value then set the Blank value for when it's populated. Do your filtering then change your beast mode back.
Re: Date Slicer - Years Only
Create a beast mode which returns YEAR(`CreatedOn`) to return the year and save it to the dataset. Alternatively you can calculate this within a dataflow.
Then use this new field in your slicer filter to allow users to filter for a specific year.
Re: Beast Mode Formula is not working as expected
As a best practice I try to create separate beast modes for the numerator and denominator, and see if those are returning the values I'd expect, so I can easily troubleshoot from there to see whether there are flaws in the logic or where are things going eerie. Since all of them go to 0, it's likely the numerator is returning 0, so either Transaction Amount is returning 0 at all times or there are no entries with values for when SourceCode = 'AP', but for that you'd need to look at your source data (you can enable the Data table panel in the card to see what is being return and include those columns to better understand what might be going on)
I see the codes on the flat data you shared do not align with the codes on the pivot version you shared first, but at first glance the Beast Mode doesn't look odd.
Re: Default date filter on dashboard not affecting cards
I would click on Edit Dashboard and then on each card, click Edit Content and click on Change Filter Exceptions. Ensure that the Allow global date option is selected.
Re: Default date filter on dashboard not affecting cards
Are you using a date field in the graph? Does your data have different date values or are they all the same date? Can you post a redacted screenshot of your analyzer with the date picker expanded?
Re: Textbox Card for Filtering on Dashboard
Yes, this is possible using pfilters. Here is an article explaining pfilters:
Re: End of query request - what is wrong
Since your invoice number field is a string you need to use strings in your IN clause. You can do this by putting them in single quotes
SELECT *
FROM CHARGE_DETAIL
WHERE INVOICE_NUMBER IN ('217653436',
'217668183',
'819802440',
'820092602')
Re: End of query request - what is wrong
Where is this query being used? In general the syntax looks odd, if you care about the specific invoice numbers the general MySQL query would be:
SELECT *
FROM CHARGE_DETAIL
WHERE INVOICE_NUMBER IN (217653436,
217668183,
819802440,
820092602)
Does this helps?
Re: Case when SYNTAX
Did you try skipping the transform to null?
Case when coalesce(trim(`commitment`), '') ='' then `loan` else `commitment` end
I can't find this written anywhere, so maybe I'm making it up, but I'm pretty sure you can't THEN NULL to return nulls. I'm surprised it's not throwing a syntax error.
Re: Drill Path - working in Edit Drill path, not working in pages
@Laura_M1 Is the second level detail you're trying to see defined as a drill layer in Analyzer or the default "Data" view? If it's the latter, then drilling to it is only an option on from the card details view and not from the dashboard. In that case I suggest adding a Table chart drill layer so that it is visible when drilling from the dashboard
