Help to create an expression

[goncalo_ricardo_pereira]

Hello all,

I really need your help to create one expression...

I am trying to create an expression that counts the number of periods where the materials have a value equal to 0.

Example of the records in my fact table (As you all can see, my fact table only has the records of each material that are not equal to 0. The Master Calendar connected to my fact table that have all the days between the min date until the max date available in the application):

Material	Date	Value
B	01/05/2015	53
B	02/05/2015	35
B	03/05/2015	5
B	04/05/2015	13
B	05/05/2015	357
B	06/05/2015	3146
B	07/05/2015	34
A	08/05/2015	734
A	09/05/2015	374
A	10/05/2015	57
A	11/05/2015	2346
A	12/05/2015	7
A	13/05/2015	7345
A	14/05/2015	28
A	15/05/2015	257
A	16/05/2015	24
A	17/05/2015	2
A	18/05/2015	257
A	19/05/2015	956
A	20/05/2015	6723
A	21/05/2015	257
A	22/05/2015	752
A	23/05/2015	234
A	24/05/2015	54
A	25/05/2015	727
A	26/05/2015	226
A	27/05/2015	457
A	28/05/2015	26
B	29/05/2015	34
B	30/05/2015	754
B	31/05/2015	23

Table with the final result of the expressions:

Material	Periods=0
A	2
B	1

Periods of each Material:

A -> First Period: 01/05/2015 - 07/05/2015

       Second Period: 29/05/2015 - 31/05/2015

Total of Periods with value equal to 0 for material A is: 2

B -> First Period: 08/05/2015 - 28/05/2015

Total of Periods with value equal to 0 for material B is: 1

Anyone has any idea how can I do this expression?

Best Regards.

Gonçalo Pereira

[sunny_talwar]

Would you be able to share a sample where you have loaded in this dummy data with the calendar table??

[goncalo_ricardo_pereira]

Hello Sunindia,

Here is the sample qvw with the dummy data.

Best Regards.

Gonçalo Pereira

[swuehl]

Maybe like this:

SET DateFormat='DD/MM/YYYY';


INPUT:
LOAD Material,
    Date,
    Value
FROM
[https://community.qlik.com/thread/168723]
(html, codepage is 1252, embedded labels, table is @1);


Calendar:
LOAD Date(MinDate+iterno()) as Date
While MinDate+iterno() <= MaxDate;
LOAD max(FieldValue('Date',recno())) as MaxDate,
  min(FieldValue('Date',recno()))-1 as MinDate
AutoGenerate FieldValueCount('Date');


Let vMinDate = num(Peek('Date',0,'Calendar'));
Let vMaxDate = num(Peek('Date',-1,'Calendar'));


TEMP:
LOAD Material,
  Date,
  if(Peek(Material) <> Material, 1, rangesum(peek(Block), if(Date-peek(Date)>=2,1))) as Block
Resident INPUT
order by Material, Date asc;

LOAD Material,
  rangesum(max(Block),-1* (min(Date)>$(vMinDate)),-1*(max(Date)<$(vMaxDate)), -1) as NumPeriodsMissing
Resident TEMP
group by Material;

drop table TEMP;

[goncalo_ricardo_pereira]

Hello swuehl,

Tks for your help, but is impossible to me to replicate this option in my application. In the real data we have to many dimension (not only the material), so basically the periods may change according to the filters applied by the user.

Basically, I need to create some of the script logic into an expression...

Best Regards

Gonçalo Pereira

[swuehl · Accepted solution]

Attached is a chart based solution, sensitive to filter.

[goncalo_ricardo_pereira]

Hello swuehl,

Tks for your help...

I actually believe that that your expression is the answer... so basically, I am now trying to adapt that expression to my reality.

Best Regards

Gonçalo Pereira