nested if statement

[jadams123]

Hi Guys:

I have 100+ nested if statements...I know there is a limit to if statements

What are my alternatives to this this formula:

if(YEAR = '2017' and DAY >= 2415 and DAY <= 2421 ,'WK1',

if(YEAR = '2017' and DAY >= 3183 and DAY <= 3189 ,'WK1',

if(YEAR = '2017' and DAY >= 2422 and DAY <= 2428 ,'WK2',

if(YEAR = '2017' and DAY >= 3190 and DAY <= 3196 ,'WK2',

and so on...

Thanks,

J

[MarcoWedel]

Can you describe your data and required result in more detail (especially your DAY field)?

A substitute for these nested if() functions might be a Pick(Match())  combination, but there should be an easier solution.

thanks

regards

Marco

[jadams123]

yes basicall i am making a calculated dimension: Quarter, Period, Week, Day.

I am making a calculated dim because IT dept has told me they have other projects pending.

How would i be able to use pick match based on above if statement...

[Anonymous]

I tend to use mapping tables for situations like this. You could build it in excel or use an inline load. Essentially what you would do is load up all your dates in one column and the week values in another, then use ApplyMap to create the new column in your main expression. For example

Week_Map:

Mapping LOAD * INLINE [

    Day, Week

    2415, WK1

    2416, WK1

    2417, WK1

    2418, WK1

    2419, WK1

    2420, WK1

    2421, WK1

    etc...

];

Then simply call

ApplyMap('Week_Map', DAY, 'Missing') AS Week

given how many dates you're checking it would probably be much neater to load the mapping table from a spreadsheet.

[johnw]

Well, I don't know the pattern for other years, and I can barely make sense out of the pattern for 2017. But this produces the same results for data that's in range of your numbers, and follows A pattern beyond them. Just not sure it's the right pattern.

'WK' & if(DAY < 3183,floor((DAY-2408)/7),floor((DAY-3176)/7))

In any case, the basic idea is "use math".

[jadams123]

hmm...i want to avoid dividing by 7...

So i have a range of data and i want to get them in Wk's

"DAY" basically means a Day number...so 2415 means monday or so

how would i simply write this code without violating the limitations i have with if statement.

I just want to write:

1) Pick year 2017

2) select a range "DAY >= 2415 and DAY <= 2421"

3) = "WK1"

thanks,

J

[deepakqlikview_123]

It would be better if you can use applymap function in this case.

Thanks

[jadams123]

i dont have developer access. i think the only choice i have is expressions

[Anonymous]

Hi,

Couldn't you handle this in your calendar? A backend script would look something like:

(WeekStart(<YourDate>) - <FirstMondayOfYear>) / 7 + If(tmpWDYear = 0, 1, 2) As YearWeekNum,

    ((WeekStart(<YourDate>) - <FirstMondayOfMonth>) / 7 + 2) As MonthWeek,

    <YourDateYear>& ' ' & Quarter as YearQuarter

And then you can just use the field in the front-end

[johnw]

If 2415 is the Monday of the first week of 2017, then, uh... is May 25, 2010 day 1? Except that then 3183 would be February 9, 2019, and a Saturday. I'm sorry, but "a day number" doesn't give me any clue what those numbers actually mean.

And why do you want to avoid dividing by 7? Does it not work for some reason? What reason?