Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
natalia_sternia
New Contributor

Count values only if they had changed (in compare to the previous row in table)

Hello everyone, 

I have a problem with counting some of values - only if value in the previous row was different than in current one.

So for example from 01/06/2019 to 03/06/2019 it should be counted as 4 (4 times the value in customRManuf field has changed in this specific period). I was wondering if peek or previous function would be accurate in here?

I would be really grateful for your support..:)
QlikSenseCommunityQuestion.PNG

 

Labels (2)
6 Replies
MVP
MVP

Re: Count values only if they had changed (in compare to the previous row in table)

Where do you want this count? In script in a variable?

natalia_sternia
New Contributor

Re: Count values only if they had changed (in compare to the previous row in table)

Is there any difference where it will be counted?

 

MVP
MVP

Re: Count values only if they had changed (in compare to the previous row in table)

There would be differences in approaching the solution. If you want an additional column in the table with the count, you have to script differently (a join would be required), whereas, if you want in variable it is little different without join.

natalia_sternia
New Contributor

Re: Count values only if they had changed (in compare to the previous row in table)

If it's gonna work in variable, it can be like that
I just need show in my app this counter as a KPI indicator

MVP
MVP

Re: Count values only if they had changed (in compare to the previous row in table)

Try like:

Load *,
     If(Peek(Field)<>Field, RangeSum(Peek(Counter),1),Peek(Counter)) as Counter
From <> ; 
Let vChangeCount=Peek('Counter')-1;
natalia_sternia
New Contributor

Re: Count values only if they had changed (in compare to the previous row in table)

I tried with this:

Licznik:
Load reference1,
If(Peek(reference1)<>reference1, RangeSum(Peek(Licznik),1),Peek(Licznik)) as Licznik
Resident FACT_KPI;
Let vChangeCount=Peek('Licznik')-1;
drop Table Licznik;

But it doesn't work ..:/