ignore date dimension straight table

[sannay14]

hello all,

i try to calculate the number of refund 'HistoCdeRembDemande.CmdNum' for which the date of request 'HistoCdeRembDemande.DateDemande' is less or equal calenderdate.
In my model i have two tables:
-Calendar 

-Refund table

I have created a straight table with Calendar.Date as Dimension:

DateDemande<=  count({$<HistoCdeRembtDemande.DateDemande={"<=$(=DATE(max(Calendrier.Date)))"}>}HistoCdeRembtDemande.CmdNum)
=> I do not have the expected value because i guess that the dimension is taken into account

I tried also to add TOTAL to my expression but now the filter done on DateDemande is no more taken into account.

Could you please help?

Thanks,
Sara

[swuehl]

Try without set analysis (which is only evaluated once per chart and not per calendar date:

count( if( HistoCdeRembtDemande.DateDemande <=  Calendrier.Date, HistoCdeRembtDemande.CmdNum))

If this is not what you want, then maybe explain with some sample line of data and your expected outcome.

[sannay14]

Actually with the if condition i have not the expected value because what append is that the dimension is taken into account first then based on rows returned the filter is done.

Calendrier.Date	HistoCdeRembtDemande.CmdNum	HistoCdeRembtDemande.DateDemande	HistoCdeRembtDemande.DateDemande
21/09/2018	1222194007	23/01/2018	20/02/2018
22/09/2018	1222194007	23/01/2018	20/02/2018
23/09/2018	1222194007	23/01/2018	20/02/2018
24/09/2018	1222194007	23/01/2018	20/02/2018
21/09/2018	1220632471	14/01/2018	07/03/2018
22/09/2018	1220632471	14/01/2018	07/03/2018
23/09/2018	1220632471	14/01/2018	07/03/2018
24/09/2018	1220632471	14/01/2018	07/03/2018
21/09/2018	1228910667	02/03/2018	07/03/2018
22/09/2018	1228910667	02/03/2018	07/03/2018
23/09/2018	1228910667	02/03/2018	07/03/2018
24/09/2018	1228910667	02/03/2018	07/03/2018
21/09/2018	1229429945	07/03/2018	12/03/2018

let's take  calendrier.Date = 21/09/2018 as dimension ,

with the expression proposed  only 4 will be returned, but the expected value is 13 as all the histocde.datedemande are <=Calendrier.Date

thanks in advance

[swuehl]

You can either use a data island calendar date field (a field that is not connected to your fact table) or an AsOf approach:

INPUT:

LOAD Calendrier.Date,

    HistoCdeRembtDemande.CmdNum,

    HistoCdeRembtDemande.DateDemande,

    HistoCdeRembtDemande.DateDemande1

FROM

[https://community.qlik.com/thread/315462?sr=inbox&ru=241865]

(html, codepage is 1252, embedded labels, table is @1);

//this is only used to create a case to ignore a record for a single calendar date, but include for the other three

Concatenate

LOAD * INLINE [

Calendrier.Date, HistoCdeRembtDemande.DateDemande

21/09/2018, 22/09/2018

];

AsOf:

LOAD DISTINCT Calendrier.Date

Resident INPUT;

JOIN

LOAD Calendrier.Date as AsOfDate

Resident AsOf;

Then:

AsOfDate	Count( If(AsOfDate >= HistoCdeRembtDemande.DateDemande, HistoCdeRembtDemande.DateDemande))
	55
21/09/2018	13
22/09/2018	14
23/09/2018	14
24/09/2018	14