Year2Date function

[salto]

Hello,

well, this is my first post in the Qlik Community.

We have recently purchased the product and our partner has made most of the implementation. Anyway, we would like to do some minor changes / improvements.

One of them is this one: we have a chart that shows this year's (Year2Date) sales information versus last year's (LastYear2Date) Sales Information:

=Sum(If(year2date(DateTransactionID),SalesAmount)) //This is Current Year's info

versus

=Sum(If(year2date(DateTransactionID,-1),SalesAmount)) // This is Last Year's info (until current date)

The improvement is that I would like this information to be filtrable: if I select 2007 for the year in the filters, I would like this chart to show 2007 and 2006 information. This does not work with the current formula, so I tried to change it this way:

=Sum(If(year2date(DateTransactionID,Max(Year)-CurrentYear),SalesAmount))

// This is Current Year's info, because Max(Year)-CurrentYear = 0 for 2010 (and -1 for 2009, etc...)

VERSUS

=Sum(If(year2date(DateTransactionID,Max(Year)-CurrentYear-1),SalesAmount))

// This is Last Year's info (until current date) because Max(Year)-CurrentYear-1 = -1 for 2010 (and -2 for 2009, etc...)

But this calculation does not work inside the year2date formula (it does outside of it).

Any help on this would be highly appreciated. Many thanks in advance,

Josetxo

[johnw]

Well, there are a lot of answers depending on what you're trying to do. Like if you're trying to create a histogram of the count in each range, and the ranges are all the same size, you might do something like this:

Dimension = class(MeasureLength,10)
Expression = count(MeasureLength)

You could also do the class() function in the script for greater chart speed at the cost of making it a little harder to change, and not being able to do clever things like make a user-enterable class width.

If your ranges are not all the same size, you could do similar with nested if():

if(MeasureLength<10,dual('0-<10',0)
,if(MeasureLength<25,dual('10-<25',10)
,dual('25+',25)))

And again, this could be in the chart or the script, but I'd go with the script. If there are a lot of these ranges, you might want to make a table with the range data and interval match it onto your main table instead of having a huge nested if().

Or maybe you just want those two specific ranges as expressions. You could do that with set analysis:

count({<MeasureLength*={">=1<10"}>} MeasureLength)
count({<MeasureLength*={">=10<20"}>} MeasureLength)

And there are probably other things you could mean as well, with yet other solutions.

[#3]

Hai John,

IF i am using below process i am getting more count i.e if i am ranging from

count({<MeasureLength*={">=240<250"}>} MeasureLength) it is showing count=6 but actually the count is 4.

How i solve this?

Thnks

[johnw]

It produces the counts I would expect on the data set you gave (which incidentally are not the counts you listed, which assume <= instead of <). See attached. Give me some example data where it fails and I can try to debug it, but it looks fine to me.

One possible problem would be if MeasureLength is a key field linking two tables. Counts don't really work well in that case, and you might need to create a counter field in your main table. But MeasureLength seems unlikely to be a key field.

[#5]

Many thanks John !!