Qlik Community

Qlik Sense App Development

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

cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
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
Highlighted
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

Contributor II
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?

Highlighted
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.