That may be because you are still trying to open the problematic table in your Compound layout. Are your query filters in both parts of the union limiting the results correctly? Do you need another filter in the "NOT IN the Subquery" query? I don't see any WHERE statements in the SQL you've shown, so it looks unfiltered.…

I should have mentioned that in going to a union query, it is frequently necessary to use a pivot table instead of a table view. In the example shown above, your first 3 columns would be in the ROW drop area. Exclude the 'Test' column because it splits the records. You can then add your bar chart within the pivot table…

If there isn't a column in your query that fits the purpose, you can simply use a constant value of 1, like this: COUNT(DISTINCT saw_2 by 1). This will apply the count distinct to your whole population.

In a union query, you can create calculated columns at the header level (outside the union) by using the "Add Result Column" link. In these columns, you refer to the union columns using saw_x references (starting from the left, with saw_0): Use these to replace the calculations within the union-ed queries. You have to be…

You'll need versions of col1 and col2 that are aggregated at the same granularity as what you are charting. Based on what you've shown, the formula for "chart col1" would be SUM("table"."col1" by "Table"."Year Created").

The data values you wish to see should be in the "Vary colors by" drop area. You can change the order within the drop area, but the vertical axis measure will always be listed last. Here's an example:

As long as your desired columns are at the same granularity, you should be able to add them to your "bar" drop area. This example is a scatter chart, but the same principle applies.

You can create sub-query to filter on (Activities in Project A). Then union (combine) two queries, with one filtered on Activities in the sub-query, and the other filtered on Activities NOT in the sub-query Include a column containing the label 'Common' in the first and 'Not Common' in the second.

You could use Occam's Razor to decide.

You can open the new filter dialog on any column, then check the Convert to SQL box: You will see this: You can replace the contents with any SQL filter.

Sorry, "404...", but I don't have the option of marking your reply "helpful", because my last question wasn't on my own thread. (you might consider changing your user name)

Hi Matthias, I was working under the assumption that project ID and project name had a one-to-one relationship. Instead of the complicated case statement, you can use a filter that just looks at the left 4 characters of your concatenated prompt-variable: LEFT('@{varProjID_Name}{845_SomeProject}',4)="General -…

You can use a calculated value in both the query prompt and the filter. Create a column like: "Project Info"."Proj_ID"||' - '||Project Info"."Proj_Name". It can be used in both the prompt and the query filter. In the prompt dialog, click on this icon to paste-in your calculated column:

Oh, I think can see it better now. Are you trying to label the Customer as new or existing? If so, you should be testing more like this: CASE WHEN (SUM(FILTER( "Expenditures"."Amount" USING ("Dim Time - Transaction Date"."Federal Fiscal Year" IN (2020)) by ..Parent Party Number)) IS NOT NULL AND…

Also, I don't know the granularity of your data, but you may have to do something like: CASE WHEN (SUM(FILTER( "Expenditures"."Amount" USING ("Dim Time - Transaction Date"."Federal Fiscal Year" IN (2020))) IS NOT NULL AND FILTER("Expenditures"."Amount" USING ("Dim Time - Transaction Date"."Federal Fiscal Year" < 2020) by…

Does this box appear in your column formula? Try checking that and see if it makes a difference.

Are you adding unnecessary single quote in the filter dialog? (it adds them automatically) If you check the "Convert filter to SQL box, does the result make sense? This filter functions correctly on my data (my FY is numeric): CASE WHEN (FILTER( "Expenditures"."Amount" USING ("Dim Time - Transaction Date"."Federal Fiscal…

Are you adding unnecessary single quotes in the text field? (they are automatically added in the SQL when using the filter building dialog.) If you check the "Convert filter to SQL" box, does the filter make sense? This one functions correctly on my data (my FY is numeric): CASE WHEN (FILTER( "Expenditures"."Amount" USING…

Are you adding unnecessary single quotes in the text field? (they are automatically added in the SQL when using the filter building dialog.) If you check the "Convert filter to SQL" box, does the filter make sense? This is the SQL that functions correctly on my data (my FY is numeric): CASE WHEN (FILTER(…

Are you adding unnecessary single quotes in the text field? (they are automatically added in the SQL when using the filter building dialog.) If you check the "Convert filter to SQL" box, does the filter make sense? This is the SQL that functions correctly on my data (my FY is numeric): CASE WHEN (FILTER(…