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: 
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
dplr-rn
Partner - Master III
Partner - Master III

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)

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