Sum to a specific date per each month

[richardcinca]

Hi all,

Is there any expression that could help me calculate sum(amount)  by the 15th of each month? 

Desired output:

1/15/2021  ... total

2/15/2021  ... total

3/15/2021  ... total

.............................

12/15/2021 ... total

Thank you,

R.

[vinieme12]

please provide more information on your raw data and expected output

a sample dataset that best represents your data and a mockup of the expected output make it easier for people to understand what you are trying to achieve

[richardcinca]

Thank you for your reply Vineeth,

The raw dataset contains about 5 years of data structured like:

Example:

DATE	AMOUNT
…...	…...
12/28/2021	7
12/29/2021	6
12/30/2021	56756
12/31/2021	568
1/1/2022	423
1/2/2022	35
1/3/2022	435
1/4/2022	4534
1/5/2022	342
…...	…...

If I add Month field as a measure and sum(Amount) as a dimension in a table and filter the data by year 2024, the desired output should look like: 

Months	Amount
Jan	sum(Amount ) of  Dec15th to Jan15th
Feb	sum(Amount ) of  Jan15th to Feb15th
Mar	sum(Amount ) of  Feb15th - Mar15th
Apr	sum(Amount ) of  Mar15th to Apr15th
May	sum(Amount ) of Apr15th to May15th
….	…....
Dec	sum(Amount ) of Nov15th to Dec15th

Thank you!

[vinieme12]

Create a Custom month field in your MasterCalendar, then use this field CustomMonth in your charts

example;

let vStartDate= '2021-01-01';
let vEndDate= '2021-12-31';

Calendar:
Load
Date
,MonthName(Date) as CalendarMonth
,MonthName( Date,if(day(Date)>=15,1,0)) as CustomMonth
;
Load
date(date#('$(vStartDate)','YYYY-MM-DD')+Iterno()-1) as Date
AutoGenerate 1
while date#('$(vStartDate)','YYYY-MM-DD') +Iterno()-1 <= date#('$(vEndDate)','YYYY-MM-DD');

Refer below blog on generating master calendar based on dates available in dataset

https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

[richardcinca]

Hi, 

Thank you for your support, given the fact I'm new to qlik maybe I have done something wrong since the solution didn't work.

First I opened the Load Editor and :

Dropped CustomMonth and Amount  in a table but the output under CustomMonth was - and under Amount was the total amount.

Instead what I did was to create another column that measures the 30day rolling sum. Using the filter from the table and searching for */15/2021 will give me the the 15th of each month in 2021 but will alter the values. Is there any way to freeze the values and extract them from the table? (I know it's not the correct way but it was the only workaround I managed to do)

Thank you!