Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

salto
Valued Contributor II

Year2Date function

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

1 Solution

Accepted Solutions
salto
Valued Contributor II

Year2Date function

Hello John,

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

Cheers!

Josetxo

13 Replies
spsrk_84
Contributor III

Year2Date function

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
Valued Contributor II

Year2Date function

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
Valued Contributor II

Year2Date function

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

Regards,

Josetxo

Not applicable

Year2Date function

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.

MVP
MVP

Year2Date function

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
Valued Contributor II

Year2Date function

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 Smiley Wink Can I set up a variable in the offset?

Regards,

Josetxo

MVP
MVP

Year2Date function


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
Valued Contributor II

Year2Date function

Hello John,

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

Cheers!

Josetxo

Not applicable

Year2Date function

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

Community Browser