Robert, I do understand that, but doing it more than once reinforces the need to get it done the right way. Jerry

If you have to do this in an analysis, one way is with a filter query, as Robert Angel said. Create a simple filter query that lists all Manager 01 IDs. Then build your main analysis and add a column that simply contains 'Yes'. Filter it on Employee ID = Manager 01 ID from your filter query (using the "is based on the…

Oops. That one is for two month before the present. Use this one: "Table"."Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH,-1,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)-1),CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)),CURRENT_DATE)

This filter returns dates from the previous month: "Table"."Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH,-2,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)-1),CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_MONTH,-1,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFMONTH(CURRENT_DATE)),CURRENT_DATE))

I can't solve the problem using your initial approach, but if you pivot the data by year in your criteria window, you can do a Pct. Change column and a RANK(Total Award by Year) column. Create a 2017 column using a formula filter like: FILTER("Tablename"."Total Award" USING ("Tablename"."Year" = 2017)). Do the same for…

Glad I could help. If you mark my response as "correct", I'd appreciate it. Cheers!

I work in 11.1.1.9 and I don't believe I can assign value to a presentation variable within an analysis,as you describe your "pv_inst_type_out ". The way I would handle this, in the context of the work you have already done, I would build a filter based on the prompted pres. variable. This would look like : 'Educational…

If you are only interested in results for the UK, you can skip all of the 1/0 columns in your first step. A single column containing a much simpler CASE statement will produce what you want. CASE WHEN COUNT("Manufacturer"."Country" by "Manufacturer"."Role", Product Number)=1 AND "Manufacturer"."Country"='UK' THEN 'UK Only'…

Sorry, I missed that in your previous post.

I can't work with the OP's data, but I was looking at the syntax of his original filter. I found that this statement will run if I append it to the filter of an unrelated query: '2018 Q 1'=(CASE WHEN RIGHT('2018 Q 2', 1) = '1' THEN CAST(CAST(LEFT('2018 Q 2', 4) AS INTEGER) -1 AS CHAR(4))||' '||'Q'||' '||'4' ELSE…

34 is the default length of CHAR. Perhaps the statement should be using CAST AS CHAR(4) and CAST AS CHAR(1) where appropriate?

I don't see any single quotes that should enclose this character value in the SQL filter, in either of the versions you show. But that would throw an error, not "No Results".

I may give the data gurus ulcers , but this: The two external columns are both marked as attributes. I used EVALUATE so the results would sort correctly. Produces this (Using my numbers, not yours): "var" is a calculated item on the Period column. Used conditional data format to get the decimals. (when Period is null...)…

Another approach is to do a simple union query that joins on the rank column. Query 1 filters in Item# 1-10, with columns for Rank, Sales, and a NULL column. Query 2 filters on Items 11-20, with columns for Rank, a Null column, and Sales. Variance is calculated at the header level using saw_x calculations, like (max(saw_1…

One way to do this is to create a "Totals" line in a union query. The second leg of the union is identical to the first, except the records in the first are collapsed into a single record, by replacing "Project Number" in this example, with the word "Totals:" Conditional formatting can be applied to "Cost" that is…

I should have explained that any conversion function (such as CAST) would do the same thing, but the "TO_CHAR" function gives you control of the format. If I use evaluate('TO_CHAR(%1,%2)' as char, "Your Date", 'yyyy / mm'), it looks exactly like your original requirement:

I've had some frustration with the same issue in data models where I want to select from a limited, discrete set of archive dates, and I don't know what the archive dates are. If you apply a TO_CHAR function to the date field, such as EVALUATE('TO_CHAR(%1,%2)' as char, "Project Info"."Snapshot Date",'mm/dd/yyyy'), the…

Is this happening on a dashboard? You'll have to adjust the column or section properties, or both.

Here's another way (If you must). Works in Tables and Pivots.

Dimitri, I'm working in 11.1.1.9.5, but I've dealt with similar requirements. In my version, the one-column pivot you see here: Looks like this on the dashboard without any special formatting: Have you tried it on a dashboard?