Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
ToLaTolA
Contributor
Contributor

Range Average for Time Series?

Hello,

I am developing a Monitor to track storage-violations in a warehouse.

I have the following data available. Please find attached.

In the script I generate a new variable with

 

if([Stock-Level]<[Min-Level], 'YES') as MIN_Vio.

 

 

I want to calculate the percentage of the MIN-Violation with the function below

1-(count(MIN_Vio))/count(Material)

But it only works when filtering one specific date. How can I make sure I get the average when selection a certain time-period?

 

Thanks

 

 

1 Reply
Vegar
MVP
MVP

Your solution should not fail. It might be something else that is causing your trouble.  Try the attached qvw containing the script and charts below.

LOAD * ,
	if([Stock-Level]<[Min-Level],dual('Yes',1),dual('No',0 )) as MIN_Violations
INLINE  [
    Material, Date, Stock-Level,Min-Level
    Apples, 2019-01-01, 40, 50
    Apples, 2019-01-02, 45, 50
    Apples, 2019-01-03, 45, 50
    Apples, 2019-01-04, 51, 50
    Apples, 2019-01-05, 55, 50
    Apples, 2019-01-06, 52, 50
    Bananas, 2019-01-01, 150, 100
    Bananas, 2019-01-02, 150, 100
    Bananas, 2019-01-03, 140, 100
    Bananas, 2019-01-04, 105, 100
    Bananas, 2019-01-05, 90, 100
    Bananas, 2019-01-06, 105, 100
];

image.png