topic Set analysis issue with previous year in QlikView

Set analysis issue with previous year

dafnis14 — Mon, 22 Feb 2016 14:29:35 GMT

Hi All,

I'm struggling with undesired behavior of set analysis...

The table shows Year of service date, while the sales are calculated based on sales date range.

I added the following expression to calculate previous year sales,

Sum({<RsvLineCreateDate = {">=$(=vMinPrevYrResvDate)<=$(=vMaxPrevYrResvDate)"}>} [# of Seats/Rooms])

But this caused the previous service year to be displayed as well, which is not desired.

How can i control this?

Thanks!

Re: Set analysis issue with previous year

sunny_talwar — Mon, 22 Feb 2016 14:31:37 GMT

Instead of Set Analysis you will need Above/Below function based on ascending/descending sorting . I guess it would be easy to help if you cna share a sample with us

Re: Set analysis issue with previous year

dafnis14 — Mon, 22 Feb 2016 14:43:00 GMT

Hi,

Not sure i follow: assuming the user selected 2016. I want the table to display 2016, and show sales of 2015.

Would the Above function help?

Thanks!

Re: Set analysis issue with previous year

sunny_talwar — Mon, 22 Feb 2016 14:48:39 GMT

Yes it definitely will. You will have to use some set analysis to restrict selections. The other way would be to use AsOfDate: The As-Of Table

Re: Set analysis issue with previous year

dafnis14 — Mon, 22 Feb 2016 17:13:46 GMT

Can you please explain why does the previous year date is displayed in the dimension column

after I add the expression?

Please note that it's not the sales date - it's a different date in the same record.

Thanks!

Re: Set analysis issue with previous year

sunny_talwar — Mon, 22 Feb 2016 17:38:03 GMT

It would be much easier and faster to show you with an example of how this can be done. Would you be able to share few rows of data?

Re: Set analysis issue with previous year

dafnis14 — Mon, 22 Feb 2016 18:13:21 GMT

Here it is..

Hope it's enough..

Thanks!

Re: Set analysis issue with previous year

sunny_talwar — Mon, 22 Feb 2016 19:14:40 GMT

I don't see a quantity field and country field. I think I am going to get a self created sample to demonstrate what I am trying to say and hopefully it will help you understand.

Re: Set analysis issue with previous year

dafnis14 — Mon, 22 Feb 2016 19:21:42 GMT

Hi,

The quantity is the count of Rsv No. (order id)

You can use the field AP Dest Location Code as a dimension for a city..

Thanks!

Re: Set analysis issue with previous year

sunny_talwar — Mon, 22 Feb 2016 19:32:28 GMT

Can you check if this is what you want?

Expressions

1) =Count({<CreateYear = {$(=Max(CreateYear))}, Year, CreateDate, ServiceDate>}[Rsv No.])

2) =Above(Count({<CreateYear = {$(=Max(CreateYear)-1)}, Year, CreateDate, ServiceDate>}[Rsv No.]))

Re: Set analysis issue with previous year

dafnis14 — Mon, 22 Feb 2016 19:58:00 GMT

Hi,

Thanks so much for you kind help!

I have a question: in the document I see it differently than the screen capture you sent:

When i select 2016, It shows the previous Year 2015 too, and you don't see the quantity for that year.

So for AMS, it should have been 938, but it shows 0...

I would need to show the selected period only (i.e, 2016) and the previous year sum..

Is this feasible?

Re: Set analysis issue with previous year

sunny_talwar — Mon, 22 Feb 2016 20:02:17 GMT

I have a question: in the document I see it differently than the screen capture you sent:

What version of QlikView are you using? This might be related to use of a old version of QlikView

When i select 2016, It shows the previous Year 2015 too, and you don't see the quantity for that year.

So for AMS, it should have been 938, but it shows 0...

I would need to show the selected period only (i.e, 2016) and the previous year sum..

Is this feasible?

The fourth column you see in the table is the previous year. Isn't that what you were looking for? Current year and Previous year data next to each other?

Re: Set analysis issue with previous year

dafnis14 — Mon, 22 Feb 2016 20:06:14 GMT

Yes, of course.. but that's not what i see..

I'm using qv11.2, SR13..

Re: Set analysis issue with previous year

sunny_talwar — Mon, 22 Feb 2016 20:09:29 GMT

Did it finally work?

Re: Set analysis issue with previous year

dafnis14 — Tue, 23 Feb 2016 04:13:39 GMT

No, it didn't.

But since on your side it did, I believe the expressions are correct 🙂

I will check it on another version too..

Thanks!

Re: Set analysis issue with previous year

sunny_talwar — Tue, 23 Feb 2016 10:44:02 GMT

Can you check on the presentation tab, if all the setting match up

Re: Set analysis issue with previous year

dafnis14 — Tue, 23 Feb 2016 18:05:15 GMT

Hi Sunny,

I checked, and in SR12, or qv 12, it works fine.

The only issue is with SR13.

Should i report it as an issue?

It is a problematic situation. I will have to find a workaround..

Thanks!

Re: Set analysis issue with previous year

sunny_talwar — Tue, 23 Feb 2016 18:20:07 GMT

I would def. encourage you to report the issue to Qlik Tech.

Re: Set analysis issue with previous year

dafnis14 — Fri, 26 Feb 2016 18:36:05 GMT

Hi Sunny,

If i may, I would like your advise regarding the above expression.

(I am using qv 11, SR9):

In general, there are orders dated with next year service date.

If we select orders dates for 2015,

In the attached table we can see that the previous period reservation dates are

So the previous period is 2014:

But there are values that are shown for previous year for both service years, and I'm not sure how it's been calculated.

Maybe for such a scenario previous periods cannot be calculated?

Thanks!