topic Re: SAS VA Dynamic Difference Row in SAS Visual Analytics

SAS VA Dynamic Difference Row

seydoie — Thu, 23 Jan 2025 10:26:10 GMT

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

Re: SAS VA Dynamic Difference Row

Sam_SAS — Thu, 23 Jan 2025 15:08:06 GMT

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

Thanks,

Sam

Re: SAS VA Dynamic Difference Row

seydoie — Fri, 24 Jan 2025 11:16:40 GMT

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.

Re: SAS VA Dynamic Difference Row

seydoie — Fri, 24 Jan 2025 11:19:17 GMT

*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.

Re: SAS VA Dynamic Difference Row

Sam_SAS — Fri, 24 Jan 2025 14:43:42 GMT

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

Re: SAS VA Dynamic Difference Row

acordes — Fri, 24 Jan 2025 15:22:48 GMT

@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;

Re: SAS VA Dynamic Difference Row

Sam_SAS — Fri, 24 Jan 2025 15:29:04 GMT

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.

Re: SAS VA Dynamic Difference Row

acordes — Fri, 24 Jan 2025 15:32:01 GMT

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

'first a'n - 'last a'n

Re: SAS VA Dynamic Difference Row

seydoie — Mon, 27 Jan 2025 07:48:12 GMT

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

Re: SAS VA Dynamic Difference Row

acordes — Mon, 27 Jan 2025 09:13:34 GMT

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.

Re: SAS VA Dynamic Difference Row

seydoie — Mon, 27 Jan 2025 11:26:00 GMT

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.

Re: SAS VA Dynamic Difference Row

Sam_SAS — Mon, 27 Jan 2025 16:33:50 GMT

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)