Counting in Pivot table

[buster22]

Hi,

I have a pivot chart,I would like to be able to count every time the value changes status to urgent but not for every instance of urgent.  In the "Filtered Urgent" attachment when counted for the Machines Description I need the third row to be two and the fourth row to be one and so on.  Any help would be much appreciated as I am currently only able to count all the values of urgent which is no use to me.

Thanks

[asinha1991]

you can try nullifying if you want to hide at chart level can do this

if(above(urfield)='Urgent' and urfield='Urgent',null())

then hide all nulls

in backend  script you can use peek after doing 'order by date desc'

if(peek(urfield)='Urgent' and urfield='Urget',null())

[buster22]

Hi asinha,

Thanks for your reply !

I've attempted to implement the peek function, I was unsure how exactly to do it the way you mentioned so I tried as shown below in the code. I am unsure why it doesn't work it seems to have no effect on the urgent values and the table remains the same. Thanks for your time.

Unqualify *;

[QLik-1 Vib]:
LOAD
[Machine Description] AS [Qlik-1 Vib.Machine Description],
[Location] AS [QLik-1 Vib.Location],
[Status] As [QLik-1 Vib.Status],
[Date] As [QLik-1 Vib.Date]

FROM [lib://AttachedFiles/Report .xlsx]
(ooxml, embedded labels, table is [QLik-1 Vib]);

UrgentTemp:

LOAD

[QLik-1 Vib.Status],
[QLik-1 Vib.Date],
[Qlik-1 Vib.Machine Description]

Resident [QLik-1 Vib]
Order by [QLik-1 Vib.Date] desc;

Urgent:

Load
//[QLik-1 Vib.Status] as [QLik-1 Vib.UrgentStatus],
if(peek([QLik-1 Vib.Status])='Urgent' and [QLik-1 Vib.Status]='Urgent',null(),[QLik-1 Vib.Status]) as [QLik-1 Vib.UrgentStatus],
[QLik-1 Vib.Date] as [Qlik-1 Vib.DateDesc],
[Qlik-1 Vib.Machine Description] as [Qlik-1 vib.UrgentDescription]

Resident UrgentTemp;

[asinha1991]

you have to take into account machine description as well

in order by add

Order by [QLik-1 Vib.Date] ,[Qlik-1 Vib.Machine Description] desc;

and in peek

if(not peek([Qlik-1 Vib.Machine Description] )=[Qlik-1 Vib.Machine Description] ,[QLik-1 Vib.Status],

if (peek([QLik-1 Vib.Status])='Urgent' and [QLik-1 Vib.Status]='Urgent',null(),[QLik-1 Vib.Status])) as [QLik-1 Vib.UrgentStatus],

[buster22]

Hi,

Sorry for the late reply, I have entered what you said but the problem seems to persist. I made some slight change to how I was naming the fields as it was a bit confusing. Thanks for you help !

UrgentTemp:

LOAD

[QLik-1 Vib.Status],
[QLik-1 Vib.Date],
[Qlik-1 Vib.Machine Description]

Resident [QLik-1 Vib]
Order by [QLik-1 Vib.Date] ,[Qlik-1 Vib.Machine Description] desc;

Urgent:

Load

if(not peek([Qlik-1 Vib.Machine Description] )=[Qlik-1 Vib.Machine Description] ,[QLik-1 Vib.Status],
if (peek([QLik-1 Vib.Status])='Urgent' and [QLik-1 Vib.Status]='Urgent',null(),[QLik-1 Vib.Status])) as [QLik-1 Vib.Urgent Status],
[QLik-1 Vib.Date] as [Qlik-1 Vib.Date desc],
[Qlik-1 Vib.Machine Description] as [Qlik-1 vib.Urgent Description]

Resident UrgentTemp;

[asinha1991]

my mistake, we are changing name of source fields.

use previous instead of peek everywhere