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

[spsrk_84]

Hi,

Use the Set anlaysis to achieve the required thing.

ex

Sum( <Year=$(#=Max(Year)>,Amount)

and

Sum( <Year=$(#=Max(Year)-1>,Amount) for Previous Month ,by default it points to the Max Year in the Date filed.

[salto]

Hello ajay,

Thanks for your reply.

The problem with your suggestion is that it sums all the sales for last year, not only those which are in the 1/1/2009..23/4/2009 range.

I would need only the Year2Date vs. LastYear2Date sales (and LastYear2Date vs. LastYearDate -1 sales)

Regards,

Josetxo

[salto]

I mean: can I make the year2date offset a calculated expression, instead of a fixed value?

Regards,

Josetxo

[#5]

You could possibly set a variable called vYTD and call that through in the set analysis.

The variable definition can then use the Date/Time function InYearToDate.

Set Analysis is definitely the way to go ... I think if you get the InYearToDate function correct you may not even have to use a variable.

[johnw]

I think this:

sum({<Year={'$(=max(Year))'}>} Amount)
sum({<Year={'$(=max(Year)-1)'},Date={"<=$(=date(addmonths(max(Date),-12)))"}>} Amount)

But it depends somewhat on all the other fields on your calendar, and how you want things to behave based on selections in those other fields.

[salto]

Many thanks for your answer.

But, it does show all the sales lines for a whole year instead of the sales lines until the date of today for that year, does it?

If I select 2007 in the filters, it sums all the sales for those 2 years. And I want only the sales until April 26 for both years.

So if I keep no filter, it confronts the sales for 2010 (until today) vs the sales for 2009...

I have tried setting the Year2Date formula offset this way:

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

but it shows nothing. So I tried this:

=Sum(If(year2date(DateTransactionID,(2007-CurrentYear)),SalesAmount))

and works... but of course I cannot keep the formula this way with a fixed value 😉 Can I set up a variable in the offset?

Regards,

Josetxo

[johnw]

SALTO wrote:But, it does show all the sales lines for a whole year instead of the sales lines until the date of today for that year, does it?

No, that expression does NOT select the entire current and previous year. It did have a bug in the current year, but the result was that it only used the selected date. There was no bug in the previous year expression. It looks ONLY at the correct date range in the previous year. That's what the Date={"<= portion of it does. See the addmonths(...,-12)? It's subtracting 12 months from the date you selected, and only taking dates less than or equal to that in the previous year. But I needed to do the same thing in the CURRENT year. So this:

sum({<Year={'$(=max(Year))'},Date={"<=$(=max(Date))"}>} Amount)
sum({<Year={'$(=max(Year)-1)'},Date={"<=$(=date(addmonths(max(Date),-12)))"}>} Amount)

See attached.

[salto · Accepted solution]

Hello John,

many many thanks for your help. I have it working now.

Cheers!

Josetxo

[#10]

Hi John,

I am having a field and contains values init. i.e: MeasureLength(field) and its values(1,2,1,6,8,10,10, 12, 20,..........)

My requirement is to get a count of MeasureLength between ranges

i.e; MeasureLength >=1 and MeasureLength <10 then count should be 7

MeasureLenght >=10 and MeasureLenght <20 then count should be 4, ........

Can you help me to solve this problem