Set analysis help

[kdmarkee]

I must be something here but I cannot seem to get a certain expression to work with set analysis.  I just want to sum up valid rows where a date is <= to some other date plus some  number of days.  I have this expression but it is not giving me the results I expect, in fact all rows are zero and I don't know what I'm doing wrong.  Thanks!  

Sum({$<Date={"<=$(=[Release Date]+[Avg Days])"}>}$(expNetPaymentAmount))

[ramchalla]

@kdmarkee Sum({$<Date={"<=$(=[Release Date]+[Avg Days])"}>}$(expNetPaymentAmount))

you are adding Avg Days to the Release Date in the Set Expression and you need to make sure that it is in the same format of Date field.

you can create a text object , use the below expression and check if it is in the same format of "Date" field. if it is not in the same format, you need to modify the below expression using Date functions and use it in set analysis.

=([Release Date]+[Avg Days])

it will be helpful if you can provide some sample data.

[Kushal_Chawda]

@kdmarkee  try below. It will only work if there is 1-1 relationship between Date & Release Date. Providing sample data with expected output will give you faster answers

Sum({$<Date={"=Date<=date([Release Date]+sum([Avg Days]))"}>}$(expNetPaymentAmount))

[kdmarkee]

Still working on the expression in question...  I’m hoping someone can tell me what I am missing in terms of getting the column “$ as of estimated first paymt date” to work for all scenarios (accounts) in the second chart called “desired chart”.  I’ve attached a qvw to show a very slimmed down dummy version of my data model; in real life it is actually way more complex and includes tons more fact and dimension tables, but I’ve only included what I needed to here to demonstrate the issue I am having.   

The concept of the “desired chart” is to show when the first payment was actually received vs when we estimate/predict to receive a first payment by.  On the Main tab, the top chart is “raw” data so to speak without doing anything too fancy.  (The same is true of the second tab, but that one is showing the columns as dimensions.  People might gleen insight into seeing one way vs another.)  The “desired chart” is a representation of what I will eventually want to show in production.

The yellow columns show the most granular level of detail (transactions).  In fact, from high granularity to low granularity… an account can have many sl_ids, and an sl_id can have many transactions. The blue cells in the bottom chart are flagged to tell me the value in the cell is not calculating and there’s an issue.  It is a problem because in the “desired chart”, if the column “Did we get a first paymt by the estimated first paymt date?” is Yes but the column “$ as of estimated first paymt date” is zero, that is wrong.  If the First Payment Date is before or the same day as the Estimated First Payment Date, then the column “$ as of estimated first paymt date” should have a monetary value, and it would be equal to (or greater than**) column “$ as of first paymt date”.  (**or greater than…could happen if there were transaction dates that resulted in payments after the First Payment Date but before the Estimated First Payment Date).

In my qvw, account 7710 has 2 faulty cells (the blue cells should equal the value shown in “$ as of first paymt date”, and for whatever reason if you select either one of that account’s sl_ids, then it works.  Account 5863 works correctly for all rows without having to select an sl_id.  I can’t find what makes these 2 scenarios different in terms of my expression for “$ as of estimated first paymt date”.

Side note:  The “Suppress when value is null” is enabled for dimensions fields “show fact row” and “First Payment Date” in object “desired chart”.

Looking forward to getting insight and help on this. Thanks!

[Brett_Bleess]

@Kushal_Chawda  You have time to have another look at this one, sample QVW now attached, but subset of full model, so may cause you some issues in helping I suspect, as that may impact the Set Analysis would be my hunch.  Thanks in advance as always!

Regards,
Brett