Jump to content
We've recently updated our Privacy Statement, available here. ×

How to handle Data Limit for multiple columns as well as Date Column which can contain NULL values


Kumud K
Go to solution Solved by David Boot-Olazabal,

Recommended Posts

Hi All,

Need help in Data Limit for table as well bas Bar graph visual.

In my Dashboard having two tabs - Tab1 - is table visual which contain required column with one column which has Balance information(Numerical Data) now marking one row from Tab1 would navigate us to Tab2 were we get the detail Balance information based on reference key ID's and date associated with the all the detailed Balance inform, usually Tab2 will be blank until and unless I mark any row in Tab1. 

( ex. Tab1 has ID1, Balance column which has value of -sum(based on ID)= 2000, now after marking this row will take to Tab2 which has bar graph which will show me detail of Balance how this 2000 came like - on 13-March-2022 I got 1000 and on 13-March-2024 i got 1000)

Also Tab1 is and Tab2 pint to different IL's but have linked both IL's based on Manage Relation option in Spotfire.

Also Tab2 has "Date Picker" option in text-area using JS and two buttons- 1) Current Year 2) Current Quatre - on click of any button it used to set the From Date and To Date in Date picker.

So, in order to achieve the above I had add data limiting in Bar visual - if(((If('${idDoc}'='',true,If([ID]='${idDoc}',true,false))) and (Date([Date of Balance])>=Date('${FromDate}') and Date([Date of Balance])<=Date('${ToDate}'))

-idDoc is captured based on the marked value from Tab1

-FromDate/ToDate is coming from document-property which change data based on button has chosen if nothing is selected then by default value is set to FromDate=1/1/2016 and ToDate=DateTimeNow() 

 

Failed TestCase:

1) Above logic works perfectly fine until "Date of Balance" is not NULL/EMPTY/'' but if any ID has blank Date of Balance then data doesn't show up in bar since both condition should be satisfied. If I try to put "OR" between both the condition then CurrentYear and CurrentQuater Filter condition won't give the right result.

 

Please let me know if there is way to handle this NULL in date data limit? 

 

Regards,

Kumud

image.png.5013960aef07a7325506351a5dc9ddae.png

image.thumb.png.c2acd9b22c51c966fdc0888c57d252cb.png

Link to comment
Share on other sites

  • Solution

Hi Kumud,

You can extent your data limiting formula with another if statement, where you 'capture' NULL values in the [Date of Balance].

Not sure what you want to do when the Data of Balance is NULL, but that should be handled in that particular if statement (such as a default date or skip the entire part).

Kind regards,

David

Link to comment
Share on other sites

Hi David, 

In some cases we have values in Balance column but Date of Balance is empty so when user mark this row from Tab1 then values doesn't occur in Bar Visuals since the data limit I had used is and condition.

Could you please help me with data limit formula to handle the NULL values? 

Regards,

Kumud

Link to comment
Share on other sites

Posted (edited)

Hi David, 

In some cases we have values in Balance column but Date of Balance is empty so when user mark this row from Tab1 then values doesn't occur in Bar Visuals since the data limit I had used is and condition.

Could you please help me with data limit formula to handle the NULL values and should handle the CUrrent Year and Current Quater logic button logic?

Thanks and Regards,

Kumud

Edited by Kumud K
Link to comment
Share on other sites

For example: ID=1 and 

[Date of Balance] in - 12-Mar-23, [Balance]-40 and  [Date of Balance] = 12-March-24, [Balance]=10 and [Date of Balance] = NULL, [Balance]=30

and when I click on Current Year Button output in Bar graph should be filtered as [Date of Balance]-12-march-24 and [Balance]=10

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...