topic Re: A tricky problem that I am facing in QlikView

A tricky problem that I am facing

Fri, 13 Sep 2013 01:53:39 GMT

I need to draw a graph with a date and 2 expressions.

The table structure is like this and the sample data is like this

Year	Month	Week	Day	Sales	Date
2012-2013	1	1-1	1	1000	11-Jul-12
2012-2013	1	1-1	1	300	11-Jul-12
2012-2013	1	1-1	2	500	12-Jul-12
2012-2013	1	1-1	3	200	13-Jul-12
2013-2014	1	1-1	1	2500	16-Jul-13
2013-2014	1	1-1	1	1500	16-Jul-13
2013-2014	1	1-1	2	5000	17-Jul-13
2013-2014	1	1-1	3	2500	18-Jul-13



		My chart should look like this
		Date	This Year	Last Year
		16-Jul-13	4000	1300
		17-Jul-13	5000	500
		18-Jul-13	2500	200

Should get 4000 based on the 2 records for 16-Jul, and 1300 is derived based on the same month, week and day in the previous year 2012-2013. 11-Jul-12 is the equivalent day in the previous year and the sum 1300 hence. and so on.

I tried something like this for the second expression but it just gives me the same value as the 1st expression.

=Sum( {<[Sale_year]={"=SubField([Sale_year], '-')-1 & '-' & SubField([Sale_year], '-')"} >} [Sales])

Can you kindly advise. The crux of this is the year in the second expression points to the previous years but all the other columns should match. (Month, week and day).

Thanks,

Ram

Re: A tricky problem that I am facing

tresB — Fri, 13 Sep 2013 04:33:17 GMT

This is something you have to work in the script for. You have to create an additional field for last year data. As Of Date approach is the one you have to follow. PFA, hope, that would give you an idea.

Re: A tricky problem that I am facing

Fri, 13 Sep 2013 06:27:14 GMT

Thanks treseco. Isn't there any other simple way by which I can manipulate expression to point to a date in the previous year? My case is I need to use some 4 fields to go back and find the equivalent date last year.

Re: A tricky problem that I am facing

sujeetsingh — Fri, 13 Sep 2013 06:29:38 GMT

Tresesco,

You just written a to the point script.

Ram just what you have to do is to just maintain months and dates to in the script and you can get what you want.

Re: A tricky problem that I am facing

tresB — Fri, 13 Sep 2013 07:03:15 GMT

I won't say that 'it is impossible' to achieve in the front-end. Probably you can, using complex logic with concat function. But that would be too cumbersome. It is always recommended to use the script way. In qlikview AS Of Date is much known for the similar requirement and you would find post by John Witherspoon in the community. I would suggest you to go through the same; take the pain once and gain for ever.

Re: Re: A tricky problem that I am facing

Sun, 15 Sep 2013 23:51:12 GMT

Thanks treseco. What you say makes perfect sense. And it worked as expected. But there is still one issue. Once I change the year format from 2013 to '2013-2014', the value under 'Previous' shows the sum of 'Current' and 'Previous'. Not sure why. Can you please look at the attached?

Thanks once again.

regards!

Re: A tricky problem that I am facing

jolivares — Mon, 16 Sep 2013 01:51:23 GMT

I think that is not "possible" (not necessarily impossible in QV) because you're trying to show results over a different dimension, If you choose the day it's better:

This Year : Sum(If(Year(Date(Date#(F6,'D-MMM-YY')))=Year(Today()),F5))

Last Year : Sum(If(Year(Date(Date#(F6,'D-MMM-YY')))=Year(Today())-1,F5))

Check the initial setting for the date variable: SET DateFormat='DD/MM/YYYY';

Re: Re: Re: A tricky problem that I am facing

tresB — Mon, 16 Sep 2013 06:07:36 GMT

PFA. Hope this helps.

Re: A tricky problem that I am facing

Mon, 16 Sep 2013 06:25:47 GMT

It is the same script that Treseco provided. I value of Year is 2013-2014 instead of 2013. That is the only difference. I am hoping this to work.

@Treseco, Kindly help.

Re: A tricky problem that I am facing

Mon, 16 Sep 2013 07:04:25 GMT

Thanks a ton as usual Treseco.

Re: A tricky problem that I am facing

tresB — Mon, 16 Sep 2013 07:49:32 GMT

Sorry, i did not get the issue.

Re: A tricky problem that I am facing

Tue, 17 Sep 2013 07:32:53 GMT

I posted the message before you added the text function around Subfield. Everything working perfectly now. Thanks a ton Treseco.

Re: Re: A tricky problem that I am facing

Fri, 20 Sep 2013 03:59:46 GMT

Hi Treseco, Sorry to trouble you again. I am facing one more challenge. Now I want to display the date from the current year and not the date from the last year, though I wan to display the figures from the previous year.

If you look at the righmost chart in the attached file the chart displays 03/07/2013 and 10/07/2013 as the dates from the current year.

The chart now displays 4 rows where I expect to see only 2 rows as follows. i.e. I need to display the dates only from the current year.

DateF Year Month Week Day Current Previous

03/07/2013 2013-2014 1 1-1 1 720 220

10/07/2013 2013-2014	2	2-2	2	410 60

Thanks,

Ram

Re: A tricky problem that I am facing

tresB — Fri, 20 Sep 2013 04:41:08 GMT

Hi,

Please create a new thread to get more attention.

Re: A tricky problem that I am facing

Fri, 20 Sep 2013 04:48:15 GMT

Sure. Done.