Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
sifatnabil
Specialist
Specialist

Count if value does not exist in all previous rows

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:

count previous rows.PNG

The chart should be like this:

count previous rows graph.png

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

View solution in original post

4 Replies
MK_QSL
MVP
MVP

You need to count this in script itselt.. !

sifatnabil
Specialist
Specialist
Author

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
MVP
MVP

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
Specialist
Specialist
Author

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