Calculations based on filter
So my topic title might not truly describe what I'm looking to do here but maybe my description will make sense. I want a user to be able to enter an "As of Date" and I want to calculate datediff based on that "As of Date" filter value. If I create the SQL in the report as opposed to a view I can do this by declaring a variable:
DECLARE @as_of_date DATE = {?}
Select
CASE WHEN datediff(m, service_date, @as_of_date) <=30 THEN balance else 0 end [0-30 Days],
CASE WHEN datediff(m, service_date, @as_of_date) between 31 and 60 THEN balance else 0 end [31-60 Days]
from tableA
where service_date <= @as_of_date
I have had a bad experience working with custom queries built inside the report. My report requestors often like to add and remove fields after the report is presented and that seems to always wipe away my charts and formats. Can the above be done without the custom SQL? Or is this something not supported in my version of yellowfinbi. Is there a variable I could use to create a similar calculated field? I would very much prefer to use a view. Thanks in advanced.
The same question
Hi Larry,
This seems like something that would be easiest to explain over a quick call. Would you be interested in scheduling a time to screen-share today? If so, please let me know what works best for you (I am on MST)
Regards,
Nathan
Hi Larry,
This seems like something that would be easiest to explain over a quick call. Would you be interested in scheduling a time to screen-share today? If so, please let me know what works best for you (I am on MST)
Regards,
Nathan
how about some time tomorrow. I'm on EDT and I'm here from 8:30-5pm. Or you can call before 5pm today. Still have my number?
how about some time tomorrow. I'm on EDT and I'm here from 8:30-5pm. Or you can call before 5pm today. Still have my number?
Hi Larry,
Does 10:45 EST tomorrow work for you? Typically we will use ring central, so I can send an invitation to your email now. If this doesnt work let me know and I can switch the schedule time.
Regards,
Nathan
Hi Larry,
Does 10:45 EST tomorrow work for you? Typically we will use ring central, so I can send an invitation to your email now. If this doesnt work let me know and I can switch the schedule time.
Regards,
Nathan
Sounds good. Thank you!
From: Yellowfin Support [mailto:support@yellowfin.bi]
Sent: Thursday, May 18, 2017 3:42 PM
To: Larry Beasley
Subject: New Comment in "Calculations based on filter"
Sounds good. Thank you!
From: Yellowfin Support [mailto:support@yellowfin.bi]
Sent: Thursday, May 18, 2017 3:42 PM
To: Larry Beasley
Subject: New Comment in "Calculations based on filter"
Hi Larry,
No problem! I just realized the link I sent you was for 10:45 MST. If you just want to join the call at 10:45 EST the link should still be valid. (I will send another if not)
Regards,
Nathan
Hi Larry,
No problem! I just realized the link I sent you was for 10:45 MST. If you just want to join the call at 10:45 EST the link should still be valid. (I will send another if not)
Regards,
Nathan
Hi Larry,
Sorry for the delay here, I got sucked into another case. Here is the wiki for parameters:
http://wiki.yellowfin.com.au/display/USER72/
Regarding the hard coded date value. If you set the default to not be a dyanmic date, you are allowed to type values, and are provided a date picker:
I am going to close this off for now, but if you run into trouble, let me know!
Regards,
Nathan
Hi Larry,
Sorry for the delay here, I got sucked into another case. Here is the wiki for parameters:
http://wiki.yellowfin.com.au/display/USER72/
Regarding the hard coded date value. If you set the default to not be a dyanmic date, you are allowed to type values, and are provided a date picker:
I am going to close this off for now, but if you run into trouble, let me know!
Regards,
Nathan
Nathan,
The parameter worked like a charm for a moment. I mean, everything was GREAT then it came time to introduce a drill through and I cannot pass the value from the parameter to the related report. SO CLOSE! I even was able to create some pretty interesting reports where the report user could group by whatever they wanted. I got the parameter to help calculate the 'As of date' and all. I'm handicapped being on 7.1 and not 7.3. Unfortunately, we have no control over how our vendor updates the BI tool. Can you twist some arms and get them to update??? :)
Nathan,
The parameter worked like a charm for a moment. I mean, everything was GREAT then it came time to introduce a drill through and I cannot pass the value from the parameter to the related report. SO CLOSE! I even was able to create some pretty interesting reports where the report user could group by whatever they wanted. I got the parameter to help calculate the 'As of date' and all. I'm handicapped being on 7.1 and not 7.3. Unfortunately, we have no control over how our vendor updates the BI tool. Can you twist some arms and get them to update??? :)
Replies have been locked on this page!