help creating set analysis from if

[ellyodelly]

I have an expression:

IF ($(vDaysDiff) < 31,Sum(If(Date(Num([AR Transaction Date], '#####'))<=Date(varAgeCutoff),[AR Balance],0)))

being somewhat new to set analysis I broke it done..  and cleaned it up a bit- decided I should do the date check in number correct?

I have

vDaysDiff variable defined as

          Num(Date(varSetRunDate ,'DD-MM-YYYY')) - Num(Date([AR Transaction Date] ,'DD-MM-YYYY'))

varSetrunDate and varAgeCutoff are from an input boxes

try part 1

sum( {$< vDaysDiff ={"< 31"}>} [AR Balance])    this works

try part 2

sum( {$< num([AR Transaction Date]) ={"<=  num($varAgeCutoff}" >} [AR Balance])      I just get the - indicating an issue- 

The IF statements are working fine as long as I have the transaction date as a dimension.  I need to remove it to get a higher level of totals- and once I do that I lose any expression that references the date.  In looking at help one suggestion was to use a set analysis.  Is this the right path?  If so - what am I missing?  naturally this is the most basic of the expressions-  they do get more complicated so can I in one expression have a setAnalysis1 + setAnalysis2 + setAnalysis3?

Thanks in advance for some suggestions.

[ellyodelly]

Evan- Finally- Sorry for the delay. Great stuff. I did try to work with your prior help before looking at this solution. I believe I have a better understanding but a few questions.

Originally you mentioned that I would have to convert the cutoff date from string to date to number.. however in this final example you didn’t. Was this because once you had my example something changed? That was my guess.

You changed to have the days difference calculate on the load- the problem I have is that the users don’t have the ability to re-load the data after entering the two dates. We use access point for our users to run their dashboards. So I have to calculate that on the fly. Could I set it up as an expression and just not show it?

I am also required to show only 1 record per customer. So basically the customer totals- I cannot show all the transactions. Once I remove the tran date and the days dif- will this still work? I will play with it some more.

And regarding that tweak on the transaction date you have =Aggr(Only(), )

I have not worked much with the aggr but I believe it is creating like a subset to work with correct? But what does the only do?

_Elly

[evan_kurowski · Accepted solution]

Elly Odell wrote:

Evan-  Finally-  Sorry for the delay.  Great stuff.  I did try to work with your prior help before looking at this solution.  I believe I have a better understanding but a few questions.

Question 1:

Originally you mentioned that I would have to convert the  cutoff date from string to date to number..  however in this final example you didn’t.  Was this because once you had my example something changed?  That was my guess.

Question 2:

You changed to have the days difference calculate on the load-  the problem I have is that the users don’t have the ability to re-load the data after entering the two dates.  We use access point for our users to run their dashboards.  So I have to calculate that on the fly.  Could I set it up as an expression and just not show it?

Question 3:

I am also required to show only 1 record per customer. So basically the customer totals- I cannot show all the transactions.  Once I remove the tran date and the days dif-  will this still work?  I will play with it some more.

And regarding that tweak on the transaction date  you have =Aggr(Only(), )

  I have not worked much with the aggr but I believe it is creating like a subset to work with correct?  But what does the only do?

_Elly

Hello Elly, Here's some additional explanation on what is going on with your application with regards to the date formats and data-types.

Question 1:

As shown in the image (and you should be able to verify this in your application), you can see how using the date format string in the date fields allows some types of set-analysis comparisons, but not all of them.  Fields that are pure numeric don't seem to have this limitation.

Also, scanning your field Calendar date, you can see two different formats populating in the field.  Allowing this kind of variation to permeate into the data-model could potentially cause headaches down the road.  It can take the smallest of differences for expressions to evaluate differently, so I would unify the format across all contributors to that field.  Not guaranteeing it will cause issues... but it might.

Question 2

Not having the ability to calculate DaysDiff for each row during reload might affect the approach.

Set-analysis evaluates once per-chart (not once per chart row * which is on my wishlist*).  So in your case, if you need relative positioning results evaluated per each row in chart, then a working IF() expression in-hand might be more expedient than requiring set-analysis be involved.

Question 3:

Aggr() creates an array.  It is essentially a GroupBy function that works on the User interface available data (and it helps me to think of this as an aptly named GroupBy() function)

If you combine Aggr() with Only() it will reduce the field array to just the possible values vs. the entire field list of values.  Kind of like a GetPossibleValues() function that is compatible with set-analysis.  Because you have a set-analysis expression that will calculate a result row for every value in your dimension, if you don't use the Aggr(Only()) combo, your chart dimension will not reduce if you make a selection in your dimension field.  You can make selections in the chart on the dimension and you would not see the chart limit rows in any way, because the set-analysis overrules the field selection.

If you want the chart to reduce according to selections in the dimension  (even if you are involving an expression using set-analysis around the dimension field), using this on the dimension preserves the row-reduction behavior.

Hope some of these explanations are of use, looks like you're making progress!

~Evan

[ellyodelly]

Evan- I do apologize for not responding-  it is a work thing-  however I want to thank you for all you time and knowledge.  I am working through it all as the load allows.