Count if value does not exist in all previous rows

[sifatnabil]

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:

[MK_QSL]

You need to count this in script itselt.. !

[sifatnabil]

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.

[MK_QSL · Accepted solution]

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

[sifatnabil]

Thanks! This worked. I understood that sorting by Value and then Date made the previous() function work correctly.