Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

josecanalla
New Contributor III

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
Valued Contributor

Re: Set current MonthYear in a variable

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

11 Replies
eduardo_dimperi
Valued Contributor II

Re: Set current MonthYear in a variable

Hi Jose,

Try This:

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

carlosamonroy
Contributor III

Re: Set current MonthYear in a variable

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
New Contributor III

Re: Set current MonthYear in a variable

Not works, I get empty values.

josecanalla
New Contributor III

Re: Set current MonthYear in a variable

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
Contributor III

Re: Set current MonthYear in a variable

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
New Contributor III

Re: Set current MonthYear in a variable

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
Contributor III

Re: Set current MonthYear in a variable

‌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
Valued Contributor

Re: Set current MonthYear in a variable

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
Honored Contributor III

Re: Set current MonthYear in a variable

Try

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

Share sample app