Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
buster22
Contributor II
Contributor II

Counting in Pivot table

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

  

Labels (1)
5 Replies
asinha1991
Creator III
Creator III

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
Contributor II
Contributor II
Author

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
Creator III
Creator III

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
Contributor II
Contributor II
Author

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
Creator III
Creator III

my mistake, we are changing name of source fields.

 

use previous instead of peek everywhere