Date set expression in Measure/Dimension

[khanashique]

Hi Experts,

AIM is to see the current month data when month filter is null, and if month filter selected then particular month data to be visible.

Below is the set expression which works separately but after i joins them so only second expression works, first expression shows only total data. 

if(isnull(Month),
Sum({<Date = {">=$(=Date(Monthstart(Today())))<=$(=Date(Monthend(Today())))"}>}sales)
,Sum({<Month = {"$(=getfieldselections(Month))"}>}sales))

Another set of expression which works individually (its same as the first expression stated above)

Sum({<Date ={"=Month(today())=Month(Date)"}>}sales)

Need to know what is wrong with the expression, or should it be controlled through dimension

Thanks,

MAK.

[khanashique]

Hi,

I added YrMthPriority too, but it gives me the result same as YrMthSeq...

Thanks,

MAK.

[JordyWegman]

Hi,

That means something is not right with the formula for the YrMthPriority. Did you create the $(vCurrentYrMth) variable? Because you need that one for the comparison. If that one is empty, the YrMthPriority won't get a 1000 extra.

Jordy

Climber

[khanashique]

Yes, i have created a variable (vCurrentYrMth) in load script with master calendar ...

[JordyWegman]

That is good, what happens if you add this variable to the table? Does it only give a value for the current YrMth? And could you show me the code you use for making this variable?

Jordy

Climber

[khanashique]

Hi Jordy,

It returns only Year-Month "2019-3" and the variable i have define in script is --> Let vCurrentYrMth = Floor(Year(Today())) & '-' & Floor(Month(Today()));

Thanks,

MAK.

[JordyWegman]

Hi MAK,

This looks good, you don't  have to use the floor() in this one. But if you use:

IF(YrMth = '$(vCurrentMonth)' , YrMthSeqNr+1000, YrMthSeqNr) as YrMthPriority

Then you should get a higher number for the current YrMth. Maybe it isn't going right because we forgot '..' around the variable. If you forget this, it is going to return 2016 (because 2019 - 3 = 2016). You should return a text value.

Jordy

Climber

[khanashique]

Hi Jordy,

below expression in calendar will not work untill i create a variable for (vCurentMonth):

IF(YrMth = '$(vCurrentMonth)' , YrMthSeqNr+1000, YrMthSeqNr) as YrMthPriority

Please advise.

Thanks,

MAK.

[JordyWegman]

Hi MAK,

You should first create the calendar with the YrMthSeqNr (like you showed before). Then create a new table:

Calendar:
Load
     *,
     If(YrMth = '$(vCurrentYrMth)', YrMthSeqNr + 1000, YrMthSeqNr) as YrMthPriority
Resident MasterCalendar;

drop table MasterCalendar;

 And drop the old one. This code above I already showed you, but we didn't include the '..' around the $(vCurrentYrMth) and that was the mistake.

So do this again and it should work.

Jordy

Climber

[khanashique]

Hi Jordy,

See, now i have removed that variable which i created, and YrMthSeqNr is already part of Master Calendar.. and now i added string '' for vCurrentMonth but still result is same no changes....See below this is how my calendar script looks like.

QuartersMap:  
MAPPING LOAD   
rowno() as Month,  
'Q' & Ceil (rowno()/3) as Quarter  
AUTOGENERATE (12);  
  
LET varMinDate = Floor(YearStart(Today()));
LET varMaxDate = Floor(YearEnd(Today())+1827);
Let vToday = floor(Today());
TempCalendar:  
LOAD  
               $(varMinDate) + Iterno()-1 As Num,  
               Date($(varMinDate) + IterNo() - 1) as TempDate  
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);  
  
MasterCalendar:  
Load  
               Timestamp(TempDate, 'YYYY-MM-DD')  AS "Date",
               week(TempDate) As Week,  
               Year(TempDate) As Year,  
               Month(TempDate) As Month,  
               Day(TempDate) As Day,
               Today() - TempDate as DaysAgo,
               YeartoDate(TempDate)*-1 as CurYTDFlag,  
               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,  
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,  
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
               Year(TempDate) & '-' & Month(TempDate) As YrMth,
               12*(Year(Today())-Year(TempDate)) + Month(Today()) - Month(TempDate) as MonthsAgo,
               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,  
               WeekYear(TempDate)& '-' & Week(weekstart(TempDate)) as YearWeek,  
               WeekDay(TempDate) as WeekDay,
               if(Year(TempDate) & '-' & Month(TempDate)=Previous(Year(TempDate) & '-' & Month(TempDate)),
		 	peek(YrMthSeqNr),
            rangesum(peek(YrMthSeqNr),0) + 1) as YrMthSeqNr
Resident TempCalendar  
Order By TempDate ASC;  
Drop Table TempCalendar;  

Calendar:
Load
     *,
     If(YrMth = '$(vCurrentYrMth)', YrMthSeqNr + 1000, YrMthSeqNr) as YrMthPriority
Resident MasterCalendar;

drop table MasterCalendar;

Thanks,

MAK.

[JordyWegman]

Hi MAK,

Try this:

QuartersMap:  
MAPPING LOAD   
rowno() as Month,  
'Q' & Ceil (rowno()/3) as Quarter  
AUTOGENERATE (12);  
  
LET varMinDate = Floor(YearStart(Today()));
LET varMaxDate = Floor(YearEnd(Today())+1827);
Let vToday = floor(Today());
Let vCurrentYrMth = Year(Today()) & '-' & Month(Today());

TempCalendar:  
LOAD  
               $(varMinDate) + Iterno()-1 As Num,  
               Date($(varMinDate) + IterNo() - 1) as TempDate  
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);  
  
MasterCalendar:  
Load  
               Timestamp(TempDate, 'YYYY-MM-DD')  AS "Date",
               week(TempDate) As Week,  
               Year(TempDate) As Year,  
               Month(TempDate) As Month,  
               Day(TempDate) As Day,
               Today() - TempDate as DaysAgo,
               YeartoDate(TempDate)*-1 as CurYTDFlag,  
               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,  
               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,  
               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
               Year(TempDate) & '-' & Month(TempDate) As YrMth,
               12*(Year(Today())-Year(TempDate)) + Month(Today()) - Month(TempDate) as MonthsAgo,
               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,  
               WeekYear(TempDate)& '-' & Week(weekstart(TempDate)) as YearWeek,  
               WeekDay(TempDate) as WeekDay,
               if(Year(TempDate) & '-' & Month(TempDate)=Previous(Year(TempDate) & '-' & Month(TempDate)),
		 	peek(YrMthSeqNr),
            rangesum(peek(YrMthSeqNr),0) + 1) as YrMthSeqNr
Resident TempCalendar  
Order By TempDate ASC;  
Drop Table TempCalendar;  

Calendar:
Load
     *,
     If(YrMth = '$(vCurrentYrMth)', YrMthSeqNr + 1000, YrMthSeqNr) as YrMthPriority
Resident MasterCalendar;

drop table MasterCalendar;

I've added the new variable behind vToday.

Jordy

Climber