Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a requirement where in I need to do a YTD calculation. Pasting a snapshot of the dataset below :
CO Number | Month | Failed Changes | Product |
123 | 17-Jan | N | XYZ |
456 | 17-Feb | N | XYZ |
789 | 17-Mar | XYZ | |
111 | 17-Apr | N | XYZ |
112 | 17-May | N | XYZ |
113 | 17-Jun | XYZ | |
114 | 17-Jul | XYZ |
The requirement is calculate a YTD percentage based on the number of Failed Changes. The total number of failed changes/the total number of changes for a particular product. So I am using the following expression for calculating: Aggr(RangeSum(Above(Count(DISTINCT{<[Failed change]={'Y'}>}[Change Order Number]), 0, RowNo())),Product) / RangeSum(Above(count(DISTINCT([Change Order Number])), 0, RowNo())) But as you can see from the dataset, there are certain entries that do not have the Failed changes value populated. So this expression returns a null value. Ideally the expression should exclude the changes marked with a 'N' or a null value.
Can anyone help in providing some suggestions over this ?
Would you be able to share a sample where you are trying to do this?
In script you coud do
TMP:
Load
[CO Number],
PRODUCT,
If(Isnull([Fail changes]) OR [Fail changes]='N',0,1) AS FAIL_FLAG
from YOURQVD
COUNTERS:
Load
[CO Number],
PRODUCT,
SUM(FAIL_FLAG)/COUNT(FAIL_FLAG)*100 AS RESULT
RESIDENT TMP
GROUP BY
[CO Number],
PRODUCT