BookmarkSubscribeRSS Feed
seydoie
Fluorite | Level 6

I have a report in Visual Analytics, this report contains rows based on due dates. I want a difference row to be created based on the two due dates selected by the users and I want it to be dynamic. I need help with the tools to achieve this

11 REPLIES 11
Sam_SAS
SAS Employee

Hello, can you show us a sample of what your data looks like? That would really help us to assist you.

 

Thanks,

Sam

seydoie
Fluorite | Level 6

Hello, thank you in advance for your answer. I tried to analyse what I wanted in an excel table below. Unfortunately, I cannot share my own report due to data privacy. I hope I was able to understand. Thank you again for your help.

 

seydoie_0-1737717559032.png

 

seydoie
Fluorite | Level 6

*I hope I was able to explain it


@seydoie wrote:

Hello, thank you in advance for your answer. I tried to analyse what I wanted in an excel table below. Unfortunately, I cannot share my own report due to data privacy. I hope I was able to understand. Thank you again for your help.

 

seydoie_0-1737717559032.png

 


 

Sam_SAS
SAS Employee

Thank you, that is very helpful!

Selecting two rows in a list table and calculating the differences for all the columns, I don't think we can currently do that in VA.

This would be a good feature to request at the Product Feature Suggestions board here:

https://communities.sas.com/t5/SAS-Product-Suggestions/idb-p/product-suggestions

The product manager for VA reviews the suggestions.

Thanks,
Sam

acordes
Rhodochrosite | Level 12

@Sam_SAS I think it's possible. 

 

@seydoie if an answer solves your problem, please mark as solution. 

 

Create table for testing purpose.

data casuser.Categories(promote=yes keep= a b c d e f g _id id date);
call streaminit(4321);
format date ddmmyy10.;
array temp a b c d e f g;
array p[7] (0.2 0.3 0.1 0.15 0.05 .09 .11); /* probabilities */
do id = 1 to 100;
do over temp;
temp = 1000 * rand("Table", of p[*]); /* use OF operator */
end;
_id=put(id, best12.);
date=intnx('days', '01jun2024'd, id);
output;
end;
run;

PIC.pngpic1.pngpic2.png

Sam_SAS
SAS Employee

Clever, I couldn't think of a good way to retrieve two selections from a list table. @acordes , could you show your expression for diff_a?
You would need to create calculated items for every numeric column in the list table, I think.

acordes
Rhodochrosite | Level 12

Yes, you need calculated items for every column I think. 

 

'first a'n - 'last a'n
seydoie
Fluorite | Level 6

Thank you for your answer and help, but I want to do this development through Visual Analytics, not on the code side.

acordes
Rhodochrosite | Level 12

It's Visual Analytics, not code-driven. It's up to you to implement this in VA. 

But you need a variable that qualifies for the last and first aggregator in VA.

seydoie
Fluorite | Level 6

Thanks to your help, I made progress. I understood your solution and defined two different calculated values for the value in the first date selection and the value in the second date selection. But I could not understand how to make the selections exactly. 

Sam_SAS
SAS Employee

Here is some more detail about the solution that @acordes suggested:

 

As far as I can tell, this workaround will only work well if there is one row for each date in the data query. The user experience becomes very confusing if, for example, the row for 27JAN2025 contains the aggregation of 10 values in the underlying data query. (Because the calculation only compares the first and last row in the query, which would not match what is displayed in the list table in this case.)

 

You will need to create a filter action from the first list table to the one that displays the difference row.

 

If the user selects multiple rows, it will calculate the difference between the first and last row only. (As the user would probably expect)

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 11 replies
  • 573 views
  • 2 likes
  • 3 in conversation