Need help getting count value

[#1]

Hello,

I am having trouble figuring out how to solve my data in Qlikview. I have a chart I am going off of that is calculated just with simple cell equations. Under Open, for Jan 2017, 77 is calculated by subtracting 105 (from New - Jan 2017) by 28 (from Complete - Dec 2016). Under Open for Feb 2017, 109 is calculated by adding 77 (from Open - Jan 2017) with 54 (from New - Feb 2017). This equals 131. Then I subtract the 131 by 8 (from Complete - Feb 2017) and by 14 (from Cancelled - Feb 2017) to get to 109. The same format goes for March and April. I need to be able to do this math in Qlikview in a chart. I need it all to be under one expression so it can be one line of bars for the OPEN column. Any help is appreciated!!!

[sunny_talwar]

What are you expressions for these Columns (Open, New, Complete, Cancelled)? Would you be able to share a sample if possible?

[#3]

Unfortunately I cannot post a sample because some of the fields I am using I don't have access to copy the data. I can give you the expressions for the columns if that helps at all. The dimension is Perspective Date - Month Year, which is the date in Jan 2017, Feb 2017, (etc.) format.

Open:

COUNT ({$< [Gatekeeper.Closed Date] = {'Open'}>} distinct([Gatekeeper.Change Request Number])

New:

COUNT(DISTINCT{$<[Gatekeeper._Is New?] = {1}>} [Gatekeeper.Change Request Number])

Cancelled:

COUNT(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Cancelled'}>} [Gatekeeper.Change Request Number]))

Complete:

COUNT(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Completed'}>} [Gatekeeper.Change Request Number])

[sunny_talwar]

1 more question here... why are we starting from Jan 2017 even when we have data for 2016? Is that just as an example for us or do you want to start this with starting year?

[#5]

The 2016 data can be ignored. It was just included in the excel I am using. In the charts in Qlikview it starts at Jan 2017 and not in 2016. I have made some ugly code to represent what the expression should produce. I need to find a way to replace the numbers I have in the code with actual formulas. Those numbers represent the OPEN values from the previous year. The first chunk of formula produces the correct answer.

if([Perspective Date - Month Year] = 'Jan 2017', COUNT(DISTINCT{$<[Gatekeeper._Is New?] = {1}>} [Gatekeeper.Change Request Number]) -

COUNT(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Cancelled'}>} [Gatekeeper.Change Request Number]) -

  COUNT(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Completed'}>} [Gatekeeper.Change Request Number]),

if([Perspective Date - Month Year] = 'Feb 2017', COUNT(DISTINCT{$<[Gatekeeper._Is New?] = {1}>} [Gatekeeper.Change Request Number]) + 77 -

COUNT(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Cancelled'}>} [Gatekeeper.Change Request Number]) -

  COUNT(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Completed'}>} [Gatekeeper.Change Request Number]),

if([Perspective Date - Month Year] = 'Mar 2017', COUNT(DISTINCT{$<[Gatekeeper._Is New?] = {1}>} [Gatekeeper.Change Request Number]) + 109 -

COUNT(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Cancelled'}>} [Gatekeeper.Change Request Number]) -

  COUNT(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Completed'}>} [Gatekeeper.Change Request Number]),

if([Perspective Date - Month Year] = 'Apr 2017', COUNT(DISTINCT{$<[Gatekeeper._Is New?] = {1}>} [Gatekeeper.Change Request Number]) + 112 -

COUNT(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Cancelled'}>} [Gatekeeper.Change Request Number]) -

  COUNT(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Completed'}>} [Gatekeeper.Change Request Number])))))

[sunny_talwar]

May be this

If([Perspective Date - Month Year] = 'Jan 2017',

RangeSum(Count(DISTINCT {$<[Gatekeeper._Is New?] = {1}>} [Gatekeeper.Change Request Number]), -Count(DISTINCT {$<[Gatekeeper.Gatekeeper Status] = {'Cancelled'}>} [Gatekeeper.Change Request Number]), -Count(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Completed'}>} [Gatekeeper.Change Request Number])),

RangeSum(Above(Column(1), Count(DISTINCT {$<[Gatekeeper._Is New?] = {1}>} [Gatekeeper.Change Request Number]), -Count(DISTINCT {$<[Gatekeeper.Gatekeeper Status] = {'Cancelled'}>} [Gatekeeper.Change Request Number]), -Count(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Completed'}>} [Gatekeeper.Change Request Number])))

[#7]

It did not work . This is the output I got.

[sunny_talwar · Accepted solution]

Might have missed a parenthesis, can you try this

If([Perspective Date - Month Year] = 'Jan 2017',

RangeSum(Count(DISTINCT {$<[Gatekeeper._Is New?] = {1}>} [Gatekeeper.Change Request Number]), -Count(DISTINCT {$<[Gatekeeper.Gatekeeper Status] = {'Cancelled'}>} [Gatekeeper.Change Request Number]), -Count(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Completed'}>} [Gatekeeper.Change Request Number])),

RangeSum(Above(Column(1)), Count(DISTINCT {$<[Gatekeeper._Is New?] = {1}>} [Gatekeeper.Change Request Number]), -Count(DISTINCT {$<[Gatekeeper.Gatekeeper Status] = {'Cancelled'}>} [Gatekeeper.Change Request Number]), -Count(DISTINCT{$<[Gatekeeper.Gatekeeper Status] = {'Completed'}>} [Gatekeeper.Change Request Number])))

[#9]

It worked! Thank you!!