Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

adelmeire
New Contributor II

island Calendar and expressions

Hi,

I have this model

model.PNG

and I want a table per MonthYear with :

- the number of new cases : count({$<[Date - Open case]={">=$(=min(Date))<=$(=max(Date))"}>} distinct [Case number])

- the number of close cases : count({$<[Date - Close case]={">=$(=min(Date))<=$(=max(Date))"}>} distinct [Case number])

- the number of still open cases : count({$<[Date - Open case]={"<=$(=max(Date))"}, [Date - Close case]={">=$(=max(Date))"}>} distinct [Case number])

I don't understand my mistake because these expressions doesn't work as I which. I would like the result per month and I have the total of the year each month

resultat.PNG

Thanks for your help

8 Replies

Re: island Calendar and expressions

The reason for this are the adhoc-variables - $(=min(Date)) - within the set analysis which won't be evaluated on row-level else global for the whole chart before calculating the chart and then applied for each row. I'm not sure if your approach with using a loosen table to define your expression-filters will be successful. I suggest to consider to connect your tables with a IntervalMatch.

- Marcus

Not applicable

Re: island Calendar and expressions

Hi Anthony!

In your model there's no relation beetween the tables that's why you alway see the entire year data. You need to create a date field in the "Export" table with the same name of a field in the "Calendrier" table just to create the dependencies of the data.

Best regards,

Marcello

adelmeire
New Contributor II

Re: island Calendar and expressions

Thanks for your help.

I would like to avoid using intervalmatch because it creates too many millions of records and the size of the application will grow to fast.

Re: island Calendar and expressions

You are right that such resulting link-table could be quite big and slow down the whole application. But you could try to join the table with another table or maybe to precalculate it in any way.

- Marcus

adelmeire
New Contributor II

Re: island Calendar and expressions

Anyboby has une better solution that the intervalmatch?

The response time for my expressions is too slow with the intervalmatch. Is there any solution via a set analysis or other?

Thanks

Re: island Calendar and expressions

Try it with if-loops instead of set analysis, something like:

count(distinct if([Date - Open case] >= min(Date) and [Date - Open case] <=max(Date), [Case number]))

or maybe more performant:

if([Date - Open case] >= min(Date) and [Date - Open case] <=max(Date), count(distinct [Case number]))

- Marcus

Re: island Calendar and expressions

I have always found IntervalMatch() to perform well, even with 10+ million fact tables.

One thought though, are your dates pure dates and not timestamps formatted as dates but stored with their decimal time bits?  If so that could well cause performance issues with interval match.

adelmeire
New Contributor II

Re: island Calendar and expressions

Yes, it's a good advice.

I didn't think about that. Thanks.

Community Browser