Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a time chart of values, i.e. time is dimension and count(distinct Value) in expression. How do I count in chart only the values that never appeared in all previous rows? e.g. from [Jan to Dec], if Value='xyz' appeared in Feb, and then in March, in the time chart, it should only count xyz in Feb and not again in March.
Data looks like this:
The chart should be like this:
Temp:
Load
Date(Date#(Date,'M/DD/YYYY')) as Date,
Value
Inline
[
Date, Value
1/10/2015, abc
1/10/2015, adf
1/10/2015, aer
2/10/2015, abc
2/10/2015, adf
2/10/2015, aer
2/10/2015, xyz
3/10/2015, abc
3/10/2015, adf
3/10/2015, aer
3/10/2015, xyz
4/10/2015, abc
4/10/2015, adf
4/10/2015, aer
4/10/2015, xyz
4/10/2015, yyy
];
Final:
Load
Value,
If(Previous(Value)=Value,0,1) as Count,
Month(Date) as Month,
Date
Resident Temp
Order By Value, Date;
Drop Table Temp;
Now create a Straight Table
Dimension
Month
Expression
SUM(Count)
Presentation Tab..
Untick Suppress Zero Values
You need to count this in script itselt.. !
Thanks MRKachhiaIMP, how would I do this in script when each row in script represents a Value and a single Date? I don't have the simplified monthly view.
e.g.
Date, Value
5/10/2015, xrw
5/10/2015, gtg
5/10/2015, sssw
6/10/2015, fdg
6/10/2015, fdg
6/10/2015, fdg
I am not sure how to use peek or previous to check if value exists in previous month when the script is in single dates.
Temp:
Load
Date(Date#(Date,'M/DD/YYYY')) as Date,
Value
Inline
[
Date, Value
1/10/2015, abc
1/10/2015, adf
1/10/2015, aer
2/10/2015, abc
2/10/2015, adf
2/10/2015, aer
2/10/2015, xyz
3/10/2015, abc
3/10/2015, adf
3/10/2015, aer
3/10/2015, xyz
4/10/2015, abc
4/10/2015, adf
4/10/2015, aer
4/10/2015, xyz
4/10/2015, yyy
];
Final:
Load
Value,
If(Previous(Value)=Value,0,1) as Count,
Month(Date) as Month,
Date
Resident Temp
Order By Value, Date;
Drop Table Temp;
Now create a Straight Table
Dimension
Month
Expression
SUM(Count)
Presentation Tab..
Untick Suppress Zero Values
Thanks! This worked. I understood that sorting by Value and then Date made the previous() function work correctly.