topic Re: Date Problem in QlikView

Date Problem

Anonymous — Thu, 02 Jun 2016 18:45:07 GMT

Hi guys, my problem is:

I have two tables: CLIENT and CALENDAR
They are joined by YEAR_MONTH
CLIENTE has a column called: INCLUSION_DATE
In CALENDAR i have YEAR_MONTH, YEAR, MONTH, DAY, DATE

I would like to select YEAR=2016, MONTH=MAR and create a line chart that give me how many CLIENTs i have day by day of the whole selected month, from the day 1 to the 30.

My set analisys was:

count(

{

<INCLUSION_DATE={'<=$(=DATE)'}>

}

CLIENT)

It works when i select just one DATE. But when i select the month the chart show me nothing.

My expectation is someting like this:

Re: Date Problem

sunny_talwar — Thu, 02 Jun 2016 18:47:45 GMT

If you have already selected Year and Month, I don't think you would need Set analysis:

Count(CLIENT)

Re: Date Problem

Anonymous — Thu, 02 Jun 2016 19:03:03 GMT

No... CLIENT and CALENDAR are joined by YEAR_MONTH.

With month selected and count(CLIENT) my chart will show:

DAY from CALENDAR as Dimension: the same count value all days from the month
DAY extracted from INCLUSION_DATE as Dimension: how many CLIENT was added in each day.

And what i want is: how many CLIENT i have until each day.

Re: Date Problem

Anonymous — Fri, 03 Jun 2016 07:10:55 GMT

count(

{

<INCLUSION_DATE={'<=$(=Max(DATE))'}>

}

CLIENT)

Re: Date Problem

Kushal_Chawda — Fri, 03 Jun 2016 07:18:28 GMT

count(

{

<INCLUSION_DATE={"<=$(=max(DATE))"}, Month=>

}

CLIENT)

Re: Date Problem

Anonymous — Fri, 03 Jun 2016 12:42:25 GMT

Putting "Month=" the chart shows all dates from selected year.

Putting max(DATE) the chart shows, for all dates, the same value, the value of the max date: 31/12/2016

Re: Date Problem

Anonymous — Fri, 03 Jun 2016 12:42:45 GMT

Putting max(DATE) the chart shows, for all dates, the same value, the value of the max date: 31/03/2016

Re: Date Problem

Anonymous — Fri, 03 Jun 2016 12:46:22 GMT

Can you share your app?

Thanks!

Re: Date Problem

Anonymous — Fri, 03 Jun 2016 13:41:35 GMT

thats my app!

Thanks to all for your support!

Re: Date Problem

sunny_talwar — Fri, 03 Jun 2016 14:03:09 GMT

Can you check your sample. The YEAR_MONTH and INCLUSION_DATE are not aligning up properly here

Re: Date Problem

Anonymous — Fri, 03 Jun 2016 14:47:15 GMT

Thanks sunny.

These two fields represent different things:

INCLUSION_DATE: Represents the date my Client accepted my promotion (another table that doesn't matter for now)
YEAR_MONTH: Represents the CLIENT register datas on YEAR and MONTH selected.

Re: Date Problem

Peter_Cammaert — Fri, 03 Jun 2016 16:06:54 GMT

Your uploaded document shows something else than what you mentioned in your OP. A flat line from day one. This is to be expected as the granularity of your key connectiing Calendar to Client is at the month level, meaning that it produces the same set of clients for every day in a month. Can you add the actual date to the key and let the calendar decide on what granularity to use?

Also from your original picture it seems like you want to accumulate the number of clients. Take that into account when setting the expression properties.

Best,

Peter

Re: Date Problem

Peter_Cammaert — Fri, 03 Jun 2016 16:13:19 GMT

Also keep in mind that set analysis produces a reduced/expanded data set before the object/chart is recalculated and not a different set row-by-row in your table or chart. This accounts for your initial problem whereby the chart works for a single date (field DATE in set analysis returns a single value) and not for any other selection (DATE returns multiple values which translates to NULL in the $-substitution expression). Result: no INCLUSION_DATE matches the condition.

Best,

Peter

Re: Date Problem

Anonymous — Mon, 06 Jun 2016 17:28:06 GMT

Yeah Peter! Thanks for your attention and response.

And thats the point, the granularity. But as this change will take me a hard work (my app is not only that a sent. That are a lot of others tables in my cloud). So changing this join from CLIENT to CALENDAR will broken all of my app.

Thats the reason i'm trying to implement that vision by day using set analisys.

Thanks