Qlik Community

QlikView Administration

Discussion Board for collaboration on QlikView Management.

cancel
Showing results for 
Search instead for 
Did you mean: 
sannay14
Contributor
Contributor

ignore date dimension straight table

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

remb.png

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

2.png

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

3 Replies
swuehl
MVP
MVP

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

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.DateHistoCdeRembtDemande.CmdNumHistoCdeRembtDemande.DateDemandeHistoCdeRembtDemande.DateDemande
21/09/2018122219400723/01/201820/02/2018
22/09/2018122219400723/01/201820/02/2018
23/09/2018122219400723/01/201820/02/2018
24/09/2018122219400723/01/201820/02/2018
21/09/2018122063247114/01/201807/03/2018
22/09/2018122063247114/01/201807/03/2018
23/09/2018122063247114/01/201807/03/2018
24/09/2018122063247114/01/201807/03/2018
21/09/2018122891066702/03/201807/03/2018
22/09/2018122891066702/03/201807/03/2018
23/09/2018122891066702/03/201807/03/2018
24/09/2018122891066702/03/201807/03/2018
21/09/2018122942994507/03/201812/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
MVP
MVP

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/201813
22/09/201814
23/09/201814
24/09/201814