How to get Variance of 2 columns in Comparative analysis Dashboard?

[#1]

Hi All,

I have a comparative Analysis Dashboard.

It contains 2 columns Group 1 Employee Count and Group 2 Employee Count

Expression for Group 1 is

=Sum({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>} [Employee Count] )

Expression for Group 2 is

=Sum({$}{<[$(vYearType2)] = [Group 2] :: [$(vYearType2)],[$(vQuarterType2)] = [Group 2] :: [$(vQuarterType2)],[$(vMonthType2)] = [Group 2] :: [$(vMonthType2)],[$(vDayType2)] = [Group 2] :: [$(vDayType2)]>} [Employee Count] )

I need a 3rd column (G 1- G 2) Employee Count Variance

and I tried with below expression:

=

Sum (

({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>} [Employee Count] )

-

({$}{<[$(vYearType2)] = [Group 2] :: [$(vYearType2)],[$(vQuarterType2)] = [Group 2] :: [$(vQuarterType2)],[$(vMonthType2)] = [Group 2] :: [$(vMonthType2)],[$(vDayType2)] = [Group 2] :: [$(vDayType2)]>} [Employee Count] )

    )

but I am getting - in my column (G 1- G 2) Employee Count Variance.

There is any function to calculate row by row difference?

Note: I am see count by date, month or any other parameters.

[preminqlik]

try this ....

sum(aggr(

Sum (

({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>} [Employee Count] )

-

({$}{<[$(vYearType2)] = [Group 2] :: [$(vYearType2)],[$(vQuarterType2)] = [Group 2] :: [$(vQuarterType2)],[$(vMonthType2)] = [Group 2] :: [$(vMonthType2)],[$(vDayType2)] = [Group 2] :: [$(vDayType2)]>} [Employee Count] )

    )

,Dimension1,Dimension2,...etc))

post the sample data still if you are in need

[sagarkharpude]

You can simply use expression like:

column(1)- column(2)

No need to write the full expression to calculate the variance

[#4 · Accepted solution]

Hi,

I have more that 12 columns.

For 3rd Expression I gave =Column(1)- Column(2) it worked but for 6 expression I gave =Column(4)- Column(5) and did not work.

Then I gave Label Name  =[Column4 Label Name]- Column5 Label Name] and it worked.

Thank you