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]

This is how my script for master calendar 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;

[JordyWegman]

Hi,

What is the result you are getting?

Jordy

Climber

[khanashique]

Hi Jordy,

It does not return any result, except an "-" sign...

Thanks,

MAK.

[JordyWegman]

Hi,

Does the YrMthPriority field work as a dimension? You can add it in a table to see if it is working. Does the current month get a higher number?

Jordy

Climber

[khanashique]

Hi Jordy,

1. No i have not taken YrMthPriority  as dimension ( i am using it in measure only)

In Dimension i am using date and expression is:

if(Year=Year(Today()) and Month=Month(Today()),Date,
if(Year=GetFieldSelections(Year) and Month=GetFieldSelections(Month),Date))

and in measure i am using the following expression:

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

2. Yes as filter/table YrMthPriority  works, it returns the month value

3. current month is returning current month value only.

Please advise, if i am missing any step or doing something wrong.

Thanks,

MAK.

[JordyWegman]

Hi Khanashique,

You shouldn't use the YrMthSeqNr in the real table, but you can check if the numbers are right. This means that you should see a higher number for the current YrMth. Is this correct?

For now, don't use the complex formulas and put the following in a table:

1. Date as Dimension
2. YrMthSeqNr as Dimension
3. Sum(Sales) as Measure

Can you show a screenshot of this result showing February, March and April?

Jordy

Climber

[khanashique]

Thanks Jordy,

Please see attached file, now i have taken Date as Dimension, and have added sequence in dimension, and simple sum expression.

Note: Data in the table is 26Feb onwards...

Thanks.

MAK.

[JordyWegman]

Hi,

Could you add also the YrMthSeqNr? And do you get 1003 then for March and 2 for February and 4 for April?

Jordy

Climber

[khanashique]

Hi,

I have added YrMthSeqNr to table, and i don't get 1002 the mazimum number i got is 72..

Thanks,

MAK.

[JordyWegman]

Hi,

My bad, I meant the YrMthPriority.. Does that give a higher number?

Jordy

Climber