Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

nasjw3255
New Contributor II

Variable Pacing using rangesum or sum with set

Hi all,

I am trying to get a function together to get an idea of how I am doing in terms of getting orders

Sample Data

DateDate MonthDate YearDelivery MonthBusiness Days Remaining in MonthOrder Quantity
1/23/2018120182710
1/23/201812018375
1/23/2018120182716
1/15/20181201821320
12/29/20171220172110
12/15/2017122017285
12/19/20171220171750
12/15/20171220171810

etc.

The output I would like to see is

With 7 days remaining in January --> how many orders do we have (irregardless of month) and where were we at compared to December at the same time?

Final output desired --> using delivery month of date month +1

Date YearDate MonthDelivery MonthBusiness DaysTotal Orders
201812761
2017121760

So far I have tried the following equations --> which work only for current month and not for prior month or on year changes (i.e. looking at January in December)

Sum({<[Delivery Month]={'"$(=vMaxDateMonth+1)"},[Delivery Year]={'$(vMaxDateYear)'},

Date = {'<=$(=vMaxDate)'}>}OrderQuantity)

I have also tried using rangesum but I have not gotten it to work in any meaningful way.

Thank you

Jacob

3 Replies
MVP
MVP

Re: Variable Pacing using rangesum or sum with set

With the limited sample data this seems to work ... hacked together a bit quickly without much testing:

2018-01-24 18_33_29-QlikView x64 - [C__Users_Petter_Downloads_# QC 2018-01-24 Summary.qvw].png

nasjw3255
New Contributor II

Re: Variable Pacing using rangesum or sum with set

Petter, I am not able to get into the file you have shared to see the formula.

Are you able to explain?

MVP
MVP

Re: Variable Pacing using rangesum or sum with set

I have created a straight table:

Dimensions:

[Delivery Month] 

Expressions:

Max([Date Year])        with label Date Year

Min([Date Month])      with label Date Month

Sum({<[Business Days Remaining in Month]=>} [Order Quantity])     with label Orders

Min([Business Days Remaining in Month])     with label Business Days left

You can drag and drop the resulting columns so you get them in the right position.

I have created a list box so you can select which "Business Days Remaining in Month" you want for the two months.