Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
shree909
Partner - Specialist II
Partner - Specialist II

calculating variance in pivot table with secondary dimensionality showing -ve values

Hi folks,

i have a requirement where i want to calculate the variance in pivot table.

the issue i have is when i am using the formula as

=if(SecondaryDimensionality()<>1,

  (sum({<Year={'2015'}>}Sales)- sum({<Year={'2014'}>}Sales)),

  sum(Sales))

for the year 2014 its showing the -ve values..

i want the -ve sign only when the difference is -ve..

PFA sample application

Thanks

1 Solution

Accepted Solutions
Not applicable

Try below expression.

=if(Dimensionality()<>1,

  (sum({<Year={'2015'}>}Sales)- sum({<Year={'2014'}>}Sales)),

  sum({<Year={2015,2014}>}Sales))



I just added set analysis conditions for Sum(Sales) also to display values for 2014 and 2015 years only.

Regards,

KKR

View solution in original post

9 Replies
MK_QSL
MVP
MVP

use below expression

=FABS(if(SecondaryDimensionality()<>1,

  (sum({<Year={'2015'}>}Sales)- sum({<Year={'2014'}>}Sales)),

  sum(Sales)))

jonathandienst
Partner - Champion III
Partner - Champion III

SecondaryDimensionality only applies to the column dimensions of a cross-table structured pivot table. In your case it will always be zero.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
shree909
Partner - Specialist II
Partner - Specialist II
Author

but the variance value is poisitve which is supposded to be negative..

Not applicable

Try below expression.

=if(Dimensionality()<>1,

  (sum({<Year={'2015'}>}Sales)- sum({<Year={'2014'}>}Sales)),

  sum({<Year={2015,2014}>}Sales))



I just added set analysis conditions for Sum(Sales) also to display values for 2014 and 2015 years only.

Regards,

KKR

shree909
Partner - Specialist II
Partner - Specialist II
Author

Thanks Jonathan,

what should be the workaround to get the desired result..

MK_QSL
MVP
MVP

Use Dimensionality() instead of SecondaryDimensionality()

Not applicable

shree909‌ , were you able to resolve the issue? If yes, please post the resolution and mark any replies appropriately if they helped in the resolution.

Regards,

KKR

jonathandienst
Partner - Champion III
Partner - Champion III

I am not sure what you are trying to do.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

When dimensionality() is not equal to 1 (for Totals), he want to calculate Sales Variance between 2015 and 2014. For other values, he want to calculate Sum(sales).

Regards,

KKR