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.

[JordyWegman]

Hi Khanashique,

Maybe you can choose a different approach. Not only are if statements more complex to read, you will also make the front-end slower.

Add the following code to the back-end for your calendar. With the 2nd variable you know what the current year-month is.

Let vToday = floor(Today());

tmpCalendar:
Load
      %Date,
      YrMth,
From (Calendar source)
Where %Date = $(vToday)
;

Let vCurrentYrWk = Peek('YrMth',0,[Calendar]);

Drop table tmpCalendar;

If you have this information, check if you have a sequence number for the YrMth. If you don't, use this code below.

if(YrMth=Previous(YrMth),
		 	peek(YrMthSeqNr),
		 	rangesum(peek(YrMthSeqNr),0) + 1) as YrMthSeqNr,	

Then, load the calendar and use the variable vCurrentYrMth:

Calendar:
Load
     *,
     If(YrMth = $(vCurrentYrMth), YrMthSeqNr + 1000, YrMthSeqNr) as YrMthPriority;
From (Calendar source)
;

Having this, you can get the following formula for your calculation:

Sum({$< YrMthPriority = {"$(=MaxString(YrMthPriority))"} >}sales)

How it works, when you don't make any selections, your YrMthPriority get the currents month, because the number is the highest, example:

Name, MthNumber, YrMthPriority
February, 2, 2
March, 3, 1003
April, 4, 4

When you select a different month, all the other selected values are shown (only one month at a time!). The only drawback is that when you also select the current month and months after that, the current month will be shown.

Jordy

Climber

[khanashique]

Thanks Jordy for your explanation, but it is quite complex for me...how about using master calendar query...

but same thing here even after adding the master calendar to query the purpose is not solved...

QuartersMap:  
MAPPING LOAD   
rowno() as Month,  
'Q' & Ceil (rowno()/3) as Quarter  
AUTOGENERATE (12);  
  
// LET varMinDate = Num(MakeDate(2019,1,1));
// LET varMaxDate = Floor(num(Today())); 
  
TempCalendar:  
LOAD  
               $(varMinDate) + Iterno()-1 As Num,  
               Date($(varMinDate) + IterNo() - 1) as TempDate  
               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);  
  
MasterCalendar:  
Load  
               TempDate 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,
               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  
Resident TempCalendar  
Order By TempDate ASC;  
Drop Table TempCalendar;  

Thanks,

MAK.

[JordyWegman]

No problem, we'll do it step by step then.

First create the vToday variable:

Let vToday = floor(Today());

then create the year month combination:

Year(TempDate) & '-' & Month(TempDate) As YrMth

then add the year month sequence number:

if(Year(TempDate) & '-' & Month(TempDate)=Previous(Year(TempDate) & '-' & Month(TempDate)),
		 	peek(YrMthSeqNr),
		 	rangesum(peek(YrMthSeqNr),0) + 1) as YrMthSeqNr,

When you have added this successfully, we'll continue!

Jordy Climber

[khanashique]

Hi Jordy,
i need to know where i have to create the year month combination? in load script?
Year(TempDate) & '-' & Month(TempDate) As YrMth

[JordyWegman]

Hi,

Yes, in the MasterCalendar script you showed above.

Jordy

Climber

[JordyWegman]

Hi,

Did you manage to get it in the calendar?

Jordy

Climber

[khanashique]

Hi Jordy,

Yes, I have added the script you gave... now what is the next step...

Thanks,

MAK.

[JordyWegman]

Hi,

You need to resident load the calendar for making the YrMthPriority.

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

drop table MasterCalendar; //Otherwise you will have two calendars

 When you have this, use this formula in the front-end:

Sum({$< YrMthPriority = {"$(=MaxString(YrMthPriority))"} >}sales)

 Jordy

Climber

[khanashique]

Hi Jordy thanks for your time,

i have tried adding the above script, and tried the expression you gave but it does not give the expected result.

Sum({$<YrMthPriority = {"$(=MaxString(YrMthPriority))"}>}Sales)

Thanks,

MAK.