Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
akuligod
Contributor
Contributor

Sum within a given date range

trying to sum up sales data (with field YYYYMM) between two date ranges.  For ex: Sum of sales data between campaign date (201801) and campaign after date (201803) essentially trying to sum all sales data for the period of  3 months (before/after campaign)

Something on the lines of the formula below : any help is much appreciated.

NUM(Sum({<DataType={'Sales'},

YYYYMM={"=YYYYMM<CampaignDateAfter_YYYYMM"},

YYYYMM={"=YYYYMM>CampaignDate_YYYYMM"},

>}Sales_Cost_USD))

3 Replies
Anil_Babu_Samineni

Hiw about this?

NUM(Sum({<DataType={'Sales'},

YYYYMM={"=YYYYMM<CampaignDateAfter_YYYYMM and YYYYMM>CampaignDate_YYYYMM"},

>}Sales_Cost_USD))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vupen
Partner
Partner

If you would like to filter the YYYYMM field based on the values in the Campaign Date field for upto 3 months, you can use something like this:

Sum({<DataType={'Sales'}, YYYYMM = {">=$(=addmonths(CampaignDate_YYYYMM, 0))<=$(=addmonths(CampaignDate_YYYYMM,3))"}>} Sales_Cost_USD)

If the fields YYYYMM and/or CampaignDate_YYYYMM are not valid Date fields, you may add such fields to the data model.

akuligod
Contributor
Contributor
Author

Thank you Sir, looks like this will work.