Building a dashboard with 2 different sate to compare last year and current year data (Comparative Analysis)

[#1]

Hi Guys,

I am trying to build a dash board which can give me feasibilities to compare 2 years of data either by year, quarter, month, week or by date.

So Created 2 sets of list boxes

Group-1 which alternate state is inherit. Here created 5 list boxes each for year, quarter, month, week or by date. For all these 5 list boxes alternate state is Inherit

Group-2 which alternate state is 'ALSGroup-2'. Here created 5 list boxes each for year, quarter, month, week or by date. For all these 5 list boxes alternate state is set to 'ALSGroup-2.

Now I used a Table to display data and in expression I used Measure SUM({$}TXN_COUNT) to apply filter from Inherit state  and SUM({[ALSGroup-2]}TXN_COUNT) to apply filter from Group-2.

Now I want to apply few filters (Country, State, City, Area) to both the sates.

For example. I want to compare state VIC 2014 and 2015 TXN_COUNT (=Employees count) then I can select Vic from list box State and then year 2014 from Group-1 and year 2015 from Group-2. So here Filter VIC should apply to both the Group-1 and Group-2 and should get data like

State  Count for 2014  Count for 2015

Vic     100                  115

Where Count for 2014=SUM({$}TXN_COUNT) and Count for 2015=SUM({[ALSGroup-2]}TXN_COUNT)

How can I achieve this? What Alternate State I should select for Global List Box (Select of Which should affect both the group) ?

Message was edited by: Shivendoo Kumar

[nagarjuna_kotha]

Hi ,

Kindly Find the below attached file.

Regards,

Nagarjuna

[#3]

Thanks for the quick response.

I am expecting result as in attached screenshot.

Added another Field Region and data filter on this should affect Table data. How can I do this?

[#4]

I have achieved it by using expression

Created another Group 3 and set Alternate state of  Region to Group 3 and then used below expression:

=Sum ({< Region = [Group 3] :: Region, ProdFamily = [Group 1] :: ProdFamily>}  Qnt ) //For Getting Value from Group 3 and Group 1

=Sum ({< Region = [Group 3] :: Region, ProdFamily = [Group 1] :: ProdFamily>}  Qnt ) //For Getting Value from Group 3 and Group 2

Attached Sample .Qvw

Now Another Question..

In Group 1, 2 & 3 I have more than 1 Field and I don't want to write above expression for each field. There is any way so that I should pick and apply filter for the selection list comes under Group 1, 2 and 3.

Example:

=Sum ({< Region = [Group 3] :: Region, State= [Group 3] :: State, ,City= [Group 3] :: City, ProdFamily = [Group 1] :: ProdFamily, IV= [Group 1] :: IV>}  Qnt ) //For Getting Value from Group 3 and Group 1

Instead of above Should be something like

=Sum ({< [$Fields]= [Group 3] :: [$Fields], ,  [$Fields]= [Group 1] ::  [$Fields]>}  Qnt )

I got this working

=Sum ({[Group 1]< Region = [Group 3] :: Region>}  Qnt ) but I need something (expression and should not write for each field) all the fields from Group 3 also.

Meaning It should apply selection comes under Group 3

[#5 · Accepted solution]

Here is the Qvw File for Comparative Analysis.

I have 3 Alternate States:

Sheet=Inherit

Group1 Objects =Group 1

Group2 Objects =Group 2

Expression for Group-1: =sum({$}{<ProdFamily =[Group 1]::ProdFamily,IV =[Group 1]::IV>}Qnt)

Expression for Group-2:=Sum ({$}{<ProdFamily = [Group 2] :: ProdFamily,IV = [Group 2] :: IV>}  Qnt )

Please find the attached qvw.