How to set monthly chart based on cut of date

[Lily]

Hi, 

I've requirement to create a monthly chart based on date. How can I display data in chart based on range date below in monthly chart?  Any idea how to do this?

Jan: 1/1 - 25/1 

Feb: 25/1 - 26/2

Mac: 25/2 - 26/3

Apr: 25/3 - 26/4

May: 25/4 - 26/5

Jun: 25/5 - 26/6

Jul: 25/6 - 26/7

Aug: 25/7 - 26/8

Sep: 25/8 - 26/9

Oct: 25/9 - 26/10

Nov: 25/10 - 26/11

Dec: 25/11 - 31/12

My chart will be looks like below: 

Thank you. 

[lironbaram]

hi 

this script will give you the starting points: 

for i =0 to 660
DateList:
load Date(today()-$(i)) as dateField
AutoGenerate 1;
Next

dates:
load *,
     month(MonthYear) as Month,
     year(MonthYear) as Year;
load *,
     if(day(dateField)>25,MonthStart(addmonths(dateField,1)),MonthStart(dateField)) as MonthYear
Resident DateList;

drop Table DateList;

[Lily]

Hi @lironbaram, 

Thanks for your reply. Btw, if you noticed the date range that I'm using doesn't same for month Jan, Dec and others month (Feb-Nov). 

Jan: 1/1 - 25/1 

Dec: 25/11 - 31/12

Feb - Nov: 25/ last month- 26/current month

What I was thinking right now is to use expression and set the date range for every month in my chart using below expression: 

Jan=(sum({$<Date = {">=01/1/2020<=26/1/2020"}>}Variance))

Feb=(sum({$<Date = {">=25/1/2020<=26/2/2020"}>}Variance))

Dec=(sum({$<Date = {">=26/11/2020<=31/12/2020"}>}Variance))

Actually, the approached that I used is inconvenience because every year I have to change the date in expression. 

Is there any better approach I can use to achieve this goal?

Thanks. 

[Brett_Bleess]

Check the following Design Blog post, I think it may give you another option, but may be along the line of what the partner gave you:

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

Here is the base Design Blog link in case you want to do some further searching of you own there:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

[Lily · Accepted solution]

Hi All, 

I just wanted to let you know that this issue has been resolved. I map date with M_FISCAL_CALENDAR table and filter it by our corporate calendar variant in our HANA database. 

Btw, thank you for your efforts 🙂