Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
josecanalla
Creator
Creator

Set current MonthYear in a variable

Hello, I am using MasterCalendar and I want to filter data which is from current MonthYear.

I create this variable:

vCurrentMonthYear = Date(monthstart(Today()),'MMM-YYYY')

but when I use it in a set expression, for example like this:

SUM({MonthYear={$(vCurrentMonthYear)}} sale_total)

don't select anything. Why?

1 Solution

Accepted Solutions
luismadriz
Specialist
Specialist

Hi,

I would change the MonthYear you have in the script in Master Calendar to:

Dual(Month(TempDate)& '-' & Year(TempDate),MonthStart(TempDate)) As MonthYear


Then

vCurrentMonthYear = Month(Today())& '-' & Year(Today());

And then

=SUM({<MonthYear={"$(vCurrentMonthYear)"}>} [sale_total])

I hope this helps,

Cheers,

Luis

PS. When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

View solution in original post

11 Replies
eduardo_dimperio
Specialist II
Specialist II

Hi Jose,

Try This:

SUM({$<MonthYear={vCurrentMonthYear}>} [sale_total])

CarlosAMonroy
Creator III
Creator III

Hi Jose,

You have to make sure that the MonthYear field format is the same as the one you set in the variable.

It might be that the field is coming without dash or all values are uppercase.

E.g.

MonthYear values = Nov 2017

vCurrentMonthYear = Nov-2017

Thanks,

Carlos

josecanalla
Creator
Creator
Author

Not works, I get empty values.

josecanalla
Creator
Creator
Author

If I put vCurrentMonthYear in a KPI control I get 1/11/2017.

If I put a table with MonthYear values get:

...

oct.-2017

nov.-2017

...

But if I put Max(MonthYear) in a KPI I get 1/11/2017.

So I don't know if it is the problem.

CarlosAMonroy
Creator III
Creator III

Yes, that's the problem.

The values coming from MonthYear field are: nov.-2017 and you are setting the variable to be Nov-2017.

I recommend you to format the MonthYear field in the script to be the same format you are doing it for the variable. That way the field is set to a proper Date format and the set analysis will work.

Thanks,

Carlos

josecanalla
Creator
Creator
Author

But in my load script I have this

...

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

...

where TempDate is a Date used by the MasterCalendar. So what can I format the values?

CarlosAMonroy
Creator III
Creator III

‌Then should work the way it is. One more thing that could be, it’s to check the initial variables that are set ib the main tab. Would be great if you cab share a sample file.

luismadriz
Specialist
Specialist

Hi,

I would change the MonthYear you have in the script in Master Calendar to:

Dual(Month(TempDate)& '-' & Year(TempDate),MonthStart(TempDate)) As MonthYear


Then

vCurrentMonthYear = Month(Today())& '-' & Year(Today());

And then

=SUM({<MonthYear={"$(vCurrentMonthYear)"}>} [sale_total])

I hope this helps,

Cheers,

Luis

PS. When applicable please mark the appropriate replies as Correct. This will help community members know which discussions have already been addressed and have a possible known solution. Please mark replies as Helpful if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as Helpful if you feel additional info is useful to others

shraddha_g
Partner - Master III
Partner - Master III

Try

SUM({<MonthYear={"$(=vCurrentMonthYear)"}>} sale_total)

Share sample app