Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
jmialoundama
Creator III
Creator III

Set analysis between two date

Hi, 

I create two variable : 

/*Current month*/

LET vCurrentMonth = MonthName(addmonths(today(),-1));
/*Next 7 months*/
Let v7NextMont= MonthName(addmonths(today(),+5));

I do this set analysis to calculate amount between current months and 7 next months : 

=Sum({<[Date_rec] = {'>=$(=vCurrentMonth)<=$(=v7NextMont)'}>}[Amount])

But it's doesn't work. 

Thanks you in advance for your help

 

 

 

Labels (3)
1 Solution

Accepted Solutions
jmialoundama
Creator III
Creator III
Author

Hi @mark6505

I change my variable like this : 

/*Mois courant*/
LET vCurrentMonth=Monthstart(addmonths(Today(),0));

/*7 prochains mois*/
Let v7NextMonth=Monthstart(addmonths(Today(),5))

I obtain this two dates : 

jmialoundama_0-1655817859736.png

And my set analysis it's: 

=Sum({<[Date_rec] = {'>=$(=vCurrentMonth)<=$(=v7NextMonth)'}>}[Amount])

And it's work ! 

 

Thanks ! 🙂 

 

 

View solution in original post

4 Replies
NitinK7
Specialist
Specialist

can you add here your sample data for date and amount field

have to check your date format

jmialoundama
Creator III
Creator III
Author

Hi @NitinK7 , 

Thanks for you reply 

I have this data : 

Date_rec Amount
july 2022 30
july 2022 40
May 2021 10
April 2020 10
March 2022 5

 

Example: We are in May 2022, I need to recover the amounts from May to November 2022.

 

mark6505
Partner - Master
Partner - Master

Afternoon,

I would assume you set analysis is not work working as your variables are set to return month names. This will mean that the set analysis is trying to find a range between to string values. I.e.

Sum({<[Date_rec] = {'>=JAN<=FEB'}>}[Amount])

I believe your set analysis is correct. I would normally work with Full date on this kind of thing, so >=01/01/2022<=28/02/2022

 

Mark

jmialoundama
Creator III
Creator III
Author

Hi @mark6505

I change my variable like this : 

/*Mois courant*/
LET vCurrentMonth=Monthstart(addmonths(Today(),0));

/*7 prochains mois*/
Let v7NextMonth=Monthstart(addmonths(Today(),5))

I obtain this two dates : 

jmialoundama_0-1655817859736.png

And my set analysis it's: 

=Sum({<[Date_rec] = {'>=$(=vCurrentMonth)<=$(=v7NextMonth)'}>}[Amount])

And it's work ! 

 

Thanks ! 🙂