Trying to use measures/aggregations in if statement

[Anonymous]

Hello,

I am trying to find a way to list and count the number of items that have been invoiced for the first time in this YTD as well as the last 12 months.  I can find all items that have been invoiced in the last 12 months using:

     Count({$<[DocDate]={"$(=[DocDate] & '>=' & $(vPrevYearToday))"}>}distinct ItemCode)

     vPrevYearToday = date(addyears(), -1))

My first thought was to just add a min() around the DocDate as that would give the lowest date value that was still less than 12 months ago, but it returns "0" if i wrap it around the 2nd DocDate, and "-" if i wrap it around the first or both.

My next thought was to use a helper dimension to return a true or false dimension.  So i set up a master dimension with:

     if(min([DocDate])>=yearstart(today()),'TRUE','FALSE')

The editor says the syntax is ok, but when i try to put it in a table it says "invalid dimension".

Can you not aggregate within if statements and set analysis?  Or am i screwing up the syntax, just not enough for the editor to catch it?

[rubenmarin]

Hi Bradley, I think your variable needs a Today() or something inside the addyears, and the '-1' should go inside the function:

=date(addyears(today(), -1))

Also I'm not sure if your expression for last 12M is working properly, maybe with:

=Count({$<ItemCode=P({<[DocDate]={">=$(vPrevYearToday)"}>} ItemCode)>} distinct ItemCode)

The expression for first sale in last 12M can be:

=Count({$<ItemCode=P({<[DocDate]={">=$(vPrevYearToday)"}>} ItemCode)

    -P({<[DocDate]={"<$(vPrevYearToday)"}>} ItemCode)>} distinct ItemCode)

About the calculated dimension, you need to add an aggr that indicates where is filtering, and add an {1} to the min() to ignore selections:

=aggr(if(min({1}[DocDate])>=yearstart(today()),'TRUE','FALSE'), ItemCode)

// Selecting TRUE or FALSE will select the ItemCode(s) associated with that value; and sets the values filtered by that dimension in the chart.

Hope this helps!

[Anonymous]

Not sure I understand what the problem is, but my recommendation is to create flags in the script.  Create them in the same table where you have DocDate (the best place is master calendar, if you use it):

...

  DocDate,

  if(DocDate >= date(addyears(),-1) and DocDate <= today(), 1, 0) as Last12Mos,

  if(DocDate >= yearstart(today()) and DocDate <= today(), 1, 0) as YTD,

...

Now, your counts will be much simpler:

Count({$<Last12Mos={1}>} distinct ItemCode)

Count({$<YTD={1}>} distinct ItemCode)

Search Community for Flags for more info.

[Anonymous]

yeah the today() function was in there, but i forgot to type it out. 

I actually figured it out.  I had to wrap the DocDate and ItemCode in an aggr() in order to find the earliest docdate for each item, then count those that were less than a year old.

So my expression ended up being

count(if(aggr(min([DocDate]),ItemCode)>=addyears(today(),-1),ItemCode,null()))

And the result matched what i was expecting

[Anonymous]

Hey thanks for the response.  Finding all the documents in the last 12 mos, or YTD was something that i could already do.  The issue was finding the earliest docdate where each item was listed.  So i initially tried to just do count(min()) and it rejected it.  Then I tried to put min() in the set analysis and it rejected it again.

So i ended up finding some old blog posts that talked about doing two aggregations and settled on count(if(aggr(min([DocDate]),ItemCode)>=addyears(today(),-1),ItemCode,null()))

If I understand it correctly, just doing a min(docdate) will just find the earliest doc date, but since i wanted to find that for each item and then count the ones that matched a time period, i had to use the aggr() function to create an array of all items and their lowest doc dates to then do a check for each line in the array to see if they fell within the time period that using the if statement.

Not sure if this is the most efficient way, but it seems to work.