Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Below expression in if condition is giving wrong results for QTD and MTD but the YTD and default result is correct, QTD and MTD is giving results always the same but still wrong.
if I go for individual expressions the results is always correct for YTD,QTD, MTD and Default ,
if(Time='YTD',Sum( {<Date={">=$(=YearStart($(vMaxDate))) <=$(=$(vMaxDate))"}, Time=>} [ Value]),
if(Time='QTD',Sum( {<Date={">=$(=QuarterStart($(vMaxDate))) <=$(=$(vMaxDate))"}, Time=>} [ Value]),
if(Time='MTD',Sum( {<Date={">=$(=MonthStart($(vMaxDate))) <=$(=$(vMaxDate))"}, Time=>} [ Value]),
Sum({<Date={">=$(=AddYears(YearStart(Today()),-1)) <=$(=AddYears(YearEnd(Today()),-1))"}, Time=>} [ Value]))))
Appreciate your help.
The below expression using GetFieldSelections (Period) is fixed my problem in place of If condition or Pick match.
if(GetFieldSelections (Period)='YTD',Sum( {<%Date_Num={">=$(=Num(YearStart($(=$(vCOGSMaxDate))))) <= $(=$(vCOGSMaxDate))"}, Period=>} [COGS Value]),
if(GetFieldSelections (Period)='QTD',Sum( {<%Date_Num={">=$(=Num(QuarterStart($(=$(vCOGSMaxDate))))) <=$(=$(vCOGSMaxDate))"}, Period=>} [COGS Value]),
if(GetFieldSelections (Period)='MTD',Sum( {<%Date_Num={">=$(=Num(MonthStart($(=$(vCOGSMaxDate))))) <=$(=$(vCOGSMaxDate))"}, Period=>} [COGS Value]),
Sum( {<%Date_Num={">=$(=Num(AddYears(YearStart(Today()),-1))) <=$(=Num(AddYears(YearEnd(Today()),-1)))"}, Period=>} [COGS Value]))))
Where you are using this expression? What is dimension ? When it is giving correct results ? Can you please elaborate
I am using in table chart with only one prod dimension, if I use individual expressions like one for MTD and one for QTD in the same table chart and its giving correct results, but if I use as a single expression by using if condition, MTD and QTD results are always same but YTD and default is correct.
try using pick match
Pick (match(Time,'YTD','QTD','MTD')+1,
Sum({<Date={">=$(=AddYears(YearStart(Today()),-1)) <=$(=AddYears(YearEnd(Today()),-1))"}, Time=>} [ Value]),
Sum( {<Date={">=$(=YearStart($(vMaxDate))) <=$(=$(vMaxDate))"}, Time=>} [ Value]),
Sum( {<Date={">=$(=QuarterStart($(vMaxDate))) <=$(=$(vMaxDate))"}, Time=>} [ Value]),
Sum( {<Date={">=$(=MonthStart($(vMaxDate))) <=$(=$(vMaxDate))"}, Time=>} [ Value]))
Hi, This time I can see at least some data correct for QTD and MTD but still missing few products, if I use below individual expression in the same chart I am getting expected results. not sure why the if condition and pick match is not working as expected, is it due to below highlighted date format but individual Exp also same date format.
Sum( {<Date={">=$(=QuarterStart($(vMaxDate))) <=$(=$(vMaxDate))"}, Time=>} [ Value])
Pick match :
Individual:
Probably date format could be one reason.
What is the expression of vMaxDate variable?
Pick (match(Time,'YTD','QTD','MTD')+1,
Sum({<Date={">=$(=AddYears(YearStart(Today()),-1)) <=$(=AddYears(YearEnd(Today()),-1))"}, Time=>} [ Value]),
Sum( {<Date={">=$(=YearStart($(vMaxDate))) <=$(vMaxDate)"}, Time=>} [ Value]),
Sum( {<Date={">=$(=QuarterStart($(vMaxDate))) <=$(vMaxDate)"}, Time=>} [ Value]),
Sum( {<Date={">=$(=MonthStart($(vMaxDate))) <=$(vMaxDate)"}, Time=>} [ Value]))
below is vMaxDate formula:
Max({1<Source={'COGS'},[Metrics Type]={'COGS'}, Period=>} [Date])
Would you be able to share sample file to look at.
Is it you need to exclude selection of Period?
Pick (match(Time,'YTD','QTD','MTD')+1,
Sum({<Date={">=$(=AddYears(YearStart(Today()),-1)) <=$(=AddYears(YearEnd(Today()),-1))"}, Time=,Period=>} [ Value]),
Sum( {<Date={">=$(=YearStart($(vMaxDate))) <=$(vMaxDate)"}, Time=,Period=>} [ Value]),
Sum( {<Date={">=$(=QuarterStart($(vMaxDate))) <=$(vMaxDate)"}, Time=,Period=>} [ Value]),
Sum( {<Date={">=$(=MonthStart($(vMaxDate))) <=$(vMaxDate)"}, Time=,Period=>} [ Value]))
Actually I have multiple tables, yes I need to exclude Period for this chart since Period on different level and I have to use Period for other charts and it is a mandatory field in my app.
I tried my expressions with DateNum filed instead of Date but still the same issue, the only question I have here why Pick match or if condition is not working with multiple expressions where single expressions in Measure working fine.
Probably I could think of that your Time dimension on which you are writing the expression is not available in your chart due to which your expression is not working as expected. If you are adding the Time dimension as well in chart then for individual Time dimension value your If or pick match condition should work fine.
Single expression will always work fine because it is not dependent on any dimensional condition .
Let's take a example. I have below sample data
Data:
Load * Inline [
ID,Name,Sales
1,A,100
1,B,200
2,A,150
2,C,250
3,B,350
3,D,450 ];
I am now creating a chart with
Dimension: ID
Expression:
= if(Name ='A', sum(Sales),
if(Name ='B', sum(Sales)*10,sum(Sales)*20))
What I would expect the values for ID =1 ?
for Name ='A' (100) + Name='B' (200*10) =2100
But what I actually get is (100+200)*20 =6000
This is happening because in chart Name dimension is not available , hence if condition does not satisfy the condition for Name and goes to else part and evaluates sum(Sales)*20 expression.
Now the moment you add dimension Name in chart you will expected results.
Hope this will give you fair idea .