Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
itcapability
Contributor
Contributor

Subtract columns in pivot table

Hi,

I am struggling to subtract pivot table values from each other.

Dimension row: postcode

Dimension column: provider

Measure: avg({<Provider-={"Vi*"},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed)

(I am excluding the Providers beginning Vi)

eg.

PostcodeProvider AProvider BProvider C
AB1468
AB21086
AB3573
AB4864

 

I want to subtract all providers from Provider C, which would give:

PostcodeProvider AProvider BProvider C
AB1-4-20
AB2420
AB3240
AB4420

 

If I use a measure:

avg({<Provider-={"Vi*"},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed) - avg({<Provider-={"Vi*"},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed)

 

I get a table of zeros, as expected. However, I cannot find a way to 'fix' the second part of the set analysis to only Provider C values.  I have tried various if, if(aggr) and aggr(if) with no success. I either get zeros for Provider C and nulls for all other Providers or all nulls.

 

Thanks for your help,

Adrian

1 Solution

Accepted Solutions
sunny_talwar

I see what you are doing, try this

Avg({<Provider-={"Vi*"},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed) -
Avg(TOTAL <Postcode> {<Provider-={'C'},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed)

View solution in original post

6 Replies
dilipranjith
Partner
Partner

Is the provider C fixed?
if so did you try

avg({<Provider-={"Vi*"},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed) - avg({<Provider={'Provider C'},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed)
sunny_talwar

Why can't you just do this

Avg({<Provider-={"Vi*", 'C'},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed)

sunny_talwar

I see what you are doing, try this

Avg({<Provider-={"Vi*"},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed) -
Avg(TOTAL <Postcode> {<Provider-={'C'},date.autoCalendar.MonthsAgo={">=1<=3"}>}Speed)

View solution in original post

itcapability
Contributor
Contributor
Author

Thank you, sadly that doesn't work - I get nulls for everything.

I have tried variations but any values I get are incorrect and Provider C values are not zero (which they should be Man Happy )

sunny_talwar

Would you be able to share a sample where we can see this to be not working and do some tests?

itcapability
Contributor
Contributor
Author

Sorry, my mistake (missed out a couple of <>).

Works a treat !! I was wondering why it was giving me values for Provider C which, when added to any other provider, gave the correct answer!

I will mark as 'solved', thanks again Smiley Happy

Adrian