topic Re: Set Analysis for Dynamic Past Two Year Total in QlikView

Set Analysis for Dynamic Past Two Year Total

Fri, 26 Jul 2013 16:02:23 GMT

Hello,

I try to get dynamic sum of sale for previous two years. For example, when I select 2011, I would like to see the total sum of sale for Year 2010 and 2009, and when I select 2012, I would like to see the total sum of sale for Year 2011 and 2010. In the set analysis, which expression am I supposed to put for the field "Sale Year"?

Thank you so much!

Re: Set Analysis for Dynamic Past Two Year Total

Gysbert_Wassenaar — Fri, 26 Jul 2013 16:10:53 GMT

Try: sum({<[Sale Year]={'>=$(=only(Sale Year])-2)<$(=only([Sale Year]))'}>} sale_amount)

Note: set analysis calculates one set for the entire chart, not a set per row. That means you can't use Sale Year as dimension. You have to select exactly one year.

Re: Set Analysis for Dynamic Past Two Year Total

MayilVahanan — Fri, 26 Jul 2013 17:23:55 GMT

Try this

sum({<[Sale Year]={'>=$(=max(Sale Year])-2)<$(=max([Sale Year]))'}>} sale_amount)

Hope it helps

Re: Set Analysis for Dynamic Past Two Year Total

nizamsha — Fri, 26 Jul 2013 17:26:48 GMT

=sum({$<year={"*"},year-={"$(=Max(year))"}>}sale_amount)

Re: Set Analysis for Dynamic Past Two Year Total

Fri, 26 Jul 2013 19:25:33 GMT

So, what if I potentially have other date dimension, such as month or monthname. If I don't want the sum value to change even though I select a specific month/month name variables, should I use the following expression:

sum({<[Sale Year]={'>=$(=only(Sale Year])-2)<$(=only([Sale Year]))', [Sale Month]=, [Sale Month Name]=, [ Sale Date]=}>}?

Thanks!

Re: Set Analysis for Dynamic Past Two Year Total

Fri, 26 Jul 2013 19:26:00 GMT

sum({<[Sale Year]={'>=$(=only(Sale Year])-2)<$(=only([Sale Year]))', [Sale Month]=, [Sale Month Name]=, [ Sale Date]=}>}?

Thanks!

Re: Set Analysis for Dynamic Past Two Year Total

Fri, 26 Jul 2013 19:27:35 GMT

sum({<[Sale Year]={'>=$(=only(Sale Year])-2)<$(=only([Sale Year]))', [Sale Month]=, [Sale Month Name]=, [ Sale Date]=}>}?

My dimension in Chart is actually mm/yyyy, and I want ALL the months of a particular year show up the same total sale value for previous two year. Do you know what would be a good way to do it?

Thanks a lot!

Re: Set Analysis for Dynamic Past Two Year Total

Fri, 26 Jul 2013 19:27:56 GMT

sum({<[Sale Year]={'>=$(=only(Sale Year])-2)<$(=only([Sale Year]))', [Sale Month]=, [Sale Month Name]=, [ Sale Date]=}>}?

My dimension in Chart is actually mm/yyyy, and I want ALL the months of a particular year show up the same total sale value for previous two year. Do you know what would be a good way to do it?

Thanks a lot!

Re: Set Analysis for Dynamic Past Two Year Total

Tue, 23 Sep 2014 16:08:01 GMT

I have a similar issue, where I have a dimension called Start Week, which is the first day of the week in YYYY-MM-DD. I want to calculate based on each rows "Start Week", the previous weeks sales. So that I can do a WoW delta. I don't want to use the "Above" function because this would force the user to show two rows of data.

For example, if they selected one single start week selection, "2014-09-05" then only one row should be displayed