topic Want to use date function in set analysis in QlikView

Want to use date function in set analysis

Thu, 24 May 2012 04:17:34 GMT

I'm fairly new to Set Analysis and I'm hoping what I'm trying to do is fairly common and it's just my lack of expertise that is limiting me. I have a large data set that looks something like the below:

As of Date, Amount

10/31/11, 934

11/30/11, 305

12/31/11, 468

1/31/12, 567

2/29/12, 949

3/31/12, 945

4/30/12, 349

In reality, my data set is about 50 million rows and 130 columns but the the above illustrates what I'm trying to accomplish. What I'd like to do is figure out the correct set analysis to get the YTD sum of Amount. I know the below syntax is not correct (I'm still learning this) but I'd like to do something along the lines of:

=sum( {$<year[As of Date] = {'$(#vCurrentYear)'}>} Amount)

where vCurrentYear is a variables that =year(today()). I've been able to find on these community pages the method for doing the second part of the equation but I can't find anything that would tell me if/how I can use a date function to get the year of an [As of Date] (which is a Qlikview date). Does anyone know? If this is not possible, is there an alternative way of doing this?

Re: Want to use date function in set analysis

teempi — Thu, 24 May 2012 05:10:23 GMT

Hi,

I think you have to extract the year part of your dimension in the script. Use something like Year([As of Date]) AS [As of Date Year]. And then you can replace year[As of Date] with [As of Date Year].

-Teemu

Re: Want to use date function in set analysis

Thu, 24 May 2012 05:50:16 GMT

Hi honeycomb,

I would recommend to add a year field in your data model. which you can do using Year() function on your date field as teempi also suggested. Which will provide you more flexibility and year field will be used quite frequently as a filter when users to analysis on the data, which will allow them to select years and see data for that year only.

Generally, A calendar is loaded in Qlikview data model which contains Day, Week, Month, Monthname, Quarter, Year etc, which comes very handy to show trend lines, as well as drill downs and other analysis as well.

Also,It is possible to achieve what you want to achieve in front -end using set analysis also. In Set analysis you can not pass values on a calculated dimension. So, for this you need to do it like that.

=sum( {$< [As of Date] = {"= Year([As of Date]) = $(#vCurrentYear)"}>} Amount)

This should do the trick. Let me know, if any issue!

Ashutosh

Re: Want to use date function in set analysis

nilesh_gangurde — Thu, 24 May 2012 05:57:55 GMT

Hi Honey ,

Create the Master calender using your date field i.e.[As of Date]

then use the year field for the set analysis.

or attach the sample data with qvw file.

Regards,

Nilesh Gangurde