Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
Hi Jose,
Try This:
SUM({$<MonthYear={vCurrentMonthYear}>} [sale_total])
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
Not works, I get empty values.
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.
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
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?
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.
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
Try
SUM({<MonthYear={"$(=vCurrentMonthYear)"}>} sale_total)
Share sample app