Solved: Set analysis issue with previous year - Qlik Community

dafnis14 · ‎2016-02-22

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!

sunny_talwar · ‎2016-02-22

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?

View solution in original post

sunny_talwar · ‎2016-02-22

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

dafnis14 · ‎2016-02-22

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!

sunny_talwar · ‎2016-02-22

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

dafnis14 · ‎2016-02-22

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!

sunny_talwar · ‎2016-02-22

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?

dafnis14 · ‎2016-02-22

Here it is..

Hope it's enough..

Thanks!

sunny_talwar · ‎2016-02-22

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.

dafnis14 · ‎2016-02-22

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!

sunny_talwar · ‎2016-02-22

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.]))