Qlik Community

Ask a Question

QlikView Administration

Discussion Board for collaboration on QlikView Management.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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