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

[Kumud K]

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

[David Boot-Olazabal]

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

[Kumud K]

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

[Kumud K]

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 March 14 by Kumud K

[Kumud K]

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

[David Boot-Olazabal]

Hi Kumud,

Would it be possible to share your dashboard? That makes it easier to troubleshoot.

Kind regards,

David