Cross Table Calculation

[Sony Hejmady]

Need help in 2 Things:

1. I have Columns Current Period ,Previous Period and CM1-CM2(Current Month-Previous Month).Please find the attched cross table .

Now I need toif Current month has data 10 % increase compared to Previous Month .If yes then I have to highlight in Color Red for the below circled scenarios. Or if colroing not possible atleast calculate a new column next to CM1-CM2 and Hardcode it as Yes more than 10%.

 

2. I need to calculate Check fromCQ-PQSM(Current Quarter- Previous Quarter of last year) column which is greater than 3000 when Currency = USD.Hence below is expression which is not giving me expected result as it is refering USD currency but not validating curreny amount.

Incorrect Expression of Check :

Sum(Case when ((

Sum(case when ([period]=2011) and ([Courrency Type]="local_currency_code") then [Local_closing_balance]

when ([period]=2011) and ([Courrency Type]="Transaction_currency_code") then [Transaction_closing_balance] end) -

Sum(case when (([period]=1912) or ([period]=1913)) and ([Courrency Type]="local_currency_code") then [Local_closing_balance]

when (([period]=1912) or ([period]=1913)) and ([Courrency Type]="Transaction_currency_code") then [Transaction_closing_balance] end))>3000.0) and ([Currency]="USD") then 0 END) as [Check]

In above expression bold ones are the expression ofCQ-PQSM.

[Paul Shumaker]

I don't believe conditional coloring exists in cross tables, but you can use it in regular table visualizations.

If you must use cross tables, you could calculate a column to determine this:

CASE WHEN Current Period > (Previous Period + (Previous Period / 10)) THEN "Yes more than 10%" ELSE NULL END

For the second issue, try this:

 

SUM(CASE WHEN [Previous.Axis] > 3000 AND [Currency] = "USD THEN 0 END)