topic Re: Two years in same chart by day? in QlikView

Two years in same chart by day?

Anonymous — Tue, 16 Sep 2014 15:46:36 GMT

Hi all, hope you can help me, I have a line chart that has a date as dimension which is already in format MMM-dd in load script. I'm showing the current count based on selections and I want to add a second line with last year data at same dates but something is not working, the chart split the dates as if they had a year, see the example.

here are my expressions:

CY: =count(if(Source='Letters',[Sales Letter ID]))

LY: =count({<FY={$(=FY-1)}>} if(Source='Letters',[Sales Letter ID]))

PS: The lines looks the same because is dummy data for CY, which is ok.

Re: Two years in same chart by day?

geert_gelade — Tue, 16 Sep 2014 15:55:43 GMT

I think the year is still in your date, it's just a format.

When you put the month-date combination in a separate field, it should work.

Re: Two years in same chart by day?

Anonymous — Tue, 16 Sep 2014 15:57:23 GMT

Hello, it is in a separate field just with month-date already

Re: Two years in same chart by day?

geert_gelade — Tue, 16 Sep 2014 15:58:26 GMT

Can you upload a sample.

Re: Re: Two years in same chart by day?

Anonymous — Tue, 16 Sep 2014 16:05:04 GMT

Here you go, thanks!

Re: Two years in same chart by day?

ecolomer — Tue, 16 Sep 2014 16:08:45 GMT

Use Month (without the year) in dimension and use another dimension year

Re: Two years in same chart by day?

Anonymous — Tue, 16 Sep 2014 16:17:09 GMT

Oscar,

solution 1: add month and day fields in the table, and then use these as dimensions

solution 2: use this expression as calculated dimension:

=Month([Report Date 2]) & '-' & Day([Report Date 2])

Hope it helps.

Marc.

Re: Re: Re: Two years in same chart by day?

geert_gelade — Tue, 16 Sep 2014 16:18:31 GMT

It's an new field, but only de formatting is changed.

I changed the dimension in the chart to the expression in the picture. To see different lines, I multiplied the last year count with 2 and the sorting is according to [Report Date].

If you don't want to use a calculated dimension, you can put it in the script.

Re: Re: Two years in same chart by day?

Anonymous — Tue, 16 Sep 2014 16:30:33 GMT

Ok, this is partially working, as Jan and Feb dates are mixed, same happened in your example...

Re: Re: Two years in same chart by day?

Anonymous — Tue, 16 Sep 2014 17:34:41 GMT

Ok, solved the sort issue... weird, just added an ORDER BY clause to SQL query in script, the rest is the same. Thanks for your help!