Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
izmingazov
New Contributor III

Period comparison based on the backlog

Hello All!

Need some help.


I have expression that calculates non-closed requests:

=RangeSum(Above (Count(DISTINCT {$< DateType={'WorkItemDeadline'} >}  %WorkItemUID)

-

Count(DISTINCT {$<DateType={'ActualFinish'} >}  %WorkItemUID), 0, RowNo()))

It's working fine on the diagram with "Month-Year" dimension (for the entire period):

1.png

But i can't figure out how to display my backlog categorized by the same periods. For example, months of the current year vs previous year.

When i change dimension to the "Month" and add 2 expressions for the years:

2017:

=rangesum(Above (Count(DISTINCT {$< DateType={'WorkItemDeadline'}, Year= {2017} >}  %WorkItemUID)

-

Count(DISTINCT {$<DateType={'ActualFinish'}, Year = {2017} >}  %WorkItemUID), 0, RowNo()))

2016:

=rangesum(Above (Count(DISTINCT {$< DateType={'WorkItemDeadline'}, Year= {"<2017"} >}  %WorkItemUID)

-

Count(DISTINCT {$<DateType={'ActualFinish'}, Year = {"<2017"} >}  %WorkItemUID), 0, RowNo()))

i have uncorrect result:

2.png

Сan anyone explain how to compare backlogs in different period? Backlog for 2017 year must include previous periods backlog. Thank you.

1 Solution

Accepted Solutions

Re: Period comparison based on the backlog

Do you mean 15 or 16 for Jan 2017? If its 16, then try this

=Aggr((RangeSum(Above(TOTAL Count(DISTINCT {$< DateType={'WorkItemDeadline'}>}  %WorkItemUID)

-

Count(DISTINCT {$< DateType={'ActualFinish'} >}  %WorkItemUID), 0, RowNo(TOTAL)))) * Avg({<Year = {2017}>} 1), [Month-Year], Month)

Capture.PNG

I thought it would be 16 because it 2016 values match with 2016 values in the above chart, so I thought 2017 values should also match

Capture.PNG

But I could be wrong and might not have understood the logic. Feel free to point out if you think I have not understood your question clearly

Best,

Sunny

4 Replies

Re: Period comparison based on the backlog

Might be easier to help if you share a sample as we might need to play around to help better. Also share the numeric output you are looking to get

izmingazov
New Contributor III

Re: Period comparison based on the backlog

Sunny, thank you for reply,

i have been attached qvw.

There is a list of requests with 3 dates (creation, completion and deadline).

I accumulate general backlog (requests to be resolved) by rangesum function (top diagram).

On the bottom diagram I need to show backlog 2017 vs 2016 years. Dimension = Month Name.

First month (January in 2017) would be 15, i.e. include previous period backlog (December 2016) and include current month (-1).

Re: Period comparison based on the backlog

Do you mean 15 or 16 for Jan 2017? If its 16, then try this

=Aggr((RangeSum(Above(TOTAL Count(DISTINCT {$< DateType={'WorkItemDeadline'}>}  %WorkItemUID)

-

Count(DISTINCT {$< DateType={'ActualFinish'} >}  %WorkItemUID), 0, RowNo(TOTAL)))) * Avg({<Year = {2017}>} 1), [Month-Year], Month)

Capture.PNG

I thought it would be 16 because it 2016 values match with 2016 values in the above chart, so I thought 2017 values should also match

Capture.PNG

But I could be wrong and might not have understood the logic. Feel free to point out if you think I have not understood your question clearly

Best,

Sunny

izmingazov
New Contributor III

Re: Period comparison based on the backlog

Sunny, of course 16 for January. Thank you so much Looks like exactly what i need.