Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

oscarmqz
Contributor III

Two years in same chart by day?

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.

Capture.JPG.jpg

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.

1 Solution

Accepted Solutions
geert_gelade
Contributor

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

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].

monthday.jpg

monthday2.jpg

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

9 Replies
geert_gelade
Contributor

Re: Two years in same chart by day?

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.

oscarmqz
Contributor III

Re: Two years in same chart by day?

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

geert_gelade
Contributor

Re: Two years in same chart by day?

Can you upload a sample.

oscarmqz
Contributor III

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

Here you go, thanks!

ecolomer
Honored Contributor II

Re: Two years in same chart by day?

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

marcarreras
Valued Contributor

Re: Two years in same chart by day?

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.

geert_gelade
Contributor

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

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].

monthday.jpg

monthday2.jpg

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

oscarmqz
Contributor III

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

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

oscarmqz
Contributor III

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

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!

Community Browser