If Condition in expression is not working as expected

[samp]

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.

[Kushal_Chawda]

Where you are using this expression? What is dimension ? When it is giving correct results ? Can you please elaborate 

[samp]

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.

[Kushal_Chawda]

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]))

[samp]

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:

[Kushal_Chawda]

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]))

[samp]

below is vMaxDate formula:

Max({1<Source={'COGS'},[Metrics Type]={'COGS'}, Period=>} [Date])

[Kushal_Chawda]

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]))

[samp]

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.

[Kushal_Chawda]

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 .