topic Re: Set analysis with variable in App Development

Set analysis with variable

DaniCalifornia — Sat, 17 Jul 2021 00:38:52 GMT

Help. I am just trying to get a variable into my set analysis. I know my variable is working because I have it writing to text fields and the table I'm trying to get this set analysis into. I have looked on the forums and my syntax seems to be right, but I cannot for the life of me to get any iteration of this to give me any financials whatsoever. I have transaction dates from 1988- July 2021. The end date is 5/31/2021 right now. It should sum everything prior to 5/31/2021, but I get nothing.

Here is my pared down set analysis. It's just this piece that does not work.

=SUM({$<TransactionDate = {"<=$(vEndDate)"} >} FinancialMovement)

I suspect it has something to do with formatting?? My TransactionDate I tag as date in the load script. My variable is populated from a drop down of date values. They are in the same yyy-MM-dd format, but my transaction date has time stamps after it. But that should not matter in a <= comparison.

Can someone please tell me where I am going wrong? I'm out of ideas.

*Yes, I am using alternate states. I tried making it inherited though and no luck, and my variables pull into the table in the alternate state, so I don't believe that's the issue.

Snapshot to show - the drop downs are date selections, the text boxes are my variable outputs. I put them in the table as well. The set analysis is updating the financial movement column and I get nothing.

Re: Set analysis with variable

rubenmarin — Sun, 18 Jul 2021 07:35:54 GMT

Hi, I usually prefer to have dates that I will use in set analysis in number, so I can forget about dates format.

If you load the date as Num(Floor(TransactionDate)) as TransactionDateNum, you will end with a date in number format with the time part removed and this expression should work:

=SUM({$<TransactionDateNum = {"<=$(vEndDate)"} >} FinancialMovement)

Re: Set analysis with variable

DaniCalifornia — Mon, 19 Jul 2021 14:30:40 GMT

Thank you! I'm actually loading straight from SQL , not creating QVDs and all of that yet - because I'm still trying to get my model right. I added this to that table and tagged it as an integer:

,FLOOR(CAST([fct].[TransactionDate] AS [INT])) AS [TransactionDateNumber]

Then I wrapped my variable in a NUM() as well. Anywho, this works:

=SUM({$<TransactionDateNumber = {"<=$(NUM(vEndDate))"} >} FinancialMovement)

Geez, data types!! Thank you for the suggestion. I've got everything there, just not matching on the stinking data types. 🙂

Re: Set analysis with variable

DaniCalifornia — Tue, 20 Jul 2021 14:23:18 GMT

Oops, I lied a little. That worked to get single transactions to show, but I had to add another = between ( and NUM to get it to sum right between dates.

=SUM({$<TransactionDateNumber = {"<=$(=NUM(vEndDate))"} >} FinancialMovement)