Comparison of Aggregated values in the staright table

[kishorj1982]

Hello All,

I have a situation where I need to compare a aggregated value in each row of the straight table and if that value exceeds the Value in variable i would like to show the corresponding row.

Now,

1) I have aggregated value where the aggregation is on 5 Dimensions but the table has 2 dimensions additionally which are at lower level than the lowest granular dimension in the aggregated value.

2) problem comes when there are more than 1 value present in the table because of the 2 new dimensions.

How to compare it in straight table?

i.e. aggr comparison expression is - sum(aggr(sum(sales),a,b,c,d,e))  > vSales.

But Straight Table has Dimensions  - d,e,f,g -

When I have more than 1 value for same combination of d,e dimensions in table, the above comparison in the expression fails.

PLease help. Also, please suggest how to replace it with the Set analysis expression for better performance.

[Gysbert_Wassenaar]

I'm afraid your post does not make much sense to me. You create an aggregation at a certain level of granularity and then want to compare those aggregations at a lower level of granularity. I don't understand how that could ever show something that has any meaning.

[kishorj1982]

Hi Gysbert,

Basically I have 4 dimensions  "Ct", "Gp"  "Cs" and "FL" and I am comparing and filtering their aggregated values on weekly level and if it passes threshold, then showing up in the Straight table. Now, there is another straight table where I am using the same threshold on the same aggregated value but this table has only 2 dimensions common to that of original Table and 2 are additional dimensions. In certain cases the 2 additional dimensions "Pg" and "Cl" are granular than the 4 dimensions "Ct", "Gp"  "Cs" and "FL" I mentioned.

At that time this comparison on threshold is not working properly.

Expression for 1st Table :

=Sum(Aggr(if(sum(Sales) > $(vSales_FR),sum(Sales)), Cs,Ct,Gp,FL,Wk))

Epression for 2nd table:

=if(sum(aggr(sum(Sales),Ct,Gp,Cl,Wk,FL))> $(vSales_FR),

Sum (Sales) )

-------------------------------------------------------

In the current  Selections of the attached QVW,  is one of the issue I am facing : Here I have multiple different  "Cl"  values same combination of  [ Ct,Gp,Cl,Wk,FL]

Year	Wk	Pg	Cl	FL	Sales
					$5225
2015	37	6	12	40-CROW	$4151
2015	37	6	14	40-CROW	$1074

--------------------------------------------------------------

Basically I want to pass only those "FL" dimensions to the second chart that are selected in 1st chart based on threshold. I dont want to do it on calculated dimension - rather than I am using these expressions

I have also used all these Dimensions in both tables and tried using 2 TOTAL functions - 1 for comparison and 2nd for the actual result. and hidden the columns that I dont want. but it throws duplicates if there are any at the higher level dimensions.

Please find attached and suggest the best way to restrict the dimension "FL" with appropriate right values compared with thresold.

Thanks

[kishorj1982]

Dear experts,

Also, please suggest the better way to right these expressions i.e. using Set Analysis. As in the current state it degraded the performance....

Thanks in advance.