Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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!
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/
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!