Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
valpassos
Creator III
Creator III

Get current year through last complete month

Hi community,

I have a CurrentYearToDate variable in my script that goes like this:

LET vSetCurrentYearToDate = 'Calendar_YearMonth_Seq = {">=' & Chr(36) & '(=Min({1<Calendar_Year = {"' & Chr(36) & '(=RangeMin(Year(Today()),Max(Calendar_Year)))"}>} Calendar_YearMonth_Seq))<=' & Chr(36) & '(=RangeMin(Max(Calendar_YearMonth_Seq),Max({1<Calendar_Date={"<=' & Chr(36) & '(=Today())"}>} Calendar_YearMonth_Seq)))"},' & Chr(10) &
'Calendar_Date = {"<=' & Chr(36) & '(=Date(RangeMin(Max(Calendar_Date),Max({1<Calendar_Date={"<=' & Chr(36) & '(=Today())"}>} Calendar_Date))))"},' & Chr(10) &
'Calendar_Year = ,' & Chr(10) &
'Calendar_MonthDesc = ,' & Chr(10) &
'Calendar_Month = ,' & Chr(10) &
'Calendar_YearMonthDescShort =';

But now I want to tweak this formula so it gives me all dates of this year until last complete month.
We are in November, so I want dates from January 2018 to October 2018.

How can I accomplish this in the variable?

Thanks 🙂

Lisa

Labels (3)
3 Replies
Gysbert_Wassenaar

 

Try changing:

'Calendar_Date = {"<=' & Chr(36) & '(=Date(RangeMin(Max(Calendar_Date),Max({1<Calendar_Date={"<=' & Chr(36) & '(=Today())"}>} Calendar_Date))))"},' & Chr(10) &

to:

'Calendar_Date = {"<' & Chr(36) & '(=Date(MonthStart(Today()))"},' & Chr(10) &

 


talk is cheap, supply exceeds demand
valpassos
Creator III
Creator III
Author

Hi Gysbert,

That doesn't seem to work. When I use the expression in the set analysis, as in sum({<$(vSetCurrentYearToFullMonth)>} #Units), it gives me an '-', which means there is something wrong either with the syntax or logic of the formula.

 

Lisa

Anil_Babu_Samineni

Can you please share sample application which demon the issue?
Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

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)