topic View previous year than the one selected on 8.2 Without Set Analysis in QlikView

View previous year than the one selected on 8.2 Without Set Analysis

Thu, 03 Dec 2009 03:01:07 GMT

Hello,

I would like to view data from a previous year than the one selected. However I am on 8.2 without the ability of using Set Analysis.

For example, if 2009 is selected then 2008 data will be displayed. The following works with Set Analysis:

Sum({$<Year={$(=Only(Year)-1)}>}[Net Commission])

How would I accomplish this without Set Analysis? I really would not like to modify the script.

Thanks

-Mike

View previous year than the one selected on 8.2 Without Set Analysis

Oleg_Troyansky — Thu, 03 Dec 2009 03:25:36 GMT

Mike,

it's almost an impossible task without set analysis. You can solve it, but you will have to modify your script. So, you'll have to pick your battles...

If you are ready to modify your script, here is how you can do it:

1. Create another date field and call it "As of Date".

2. Create a table with all possible combinations of your original Date and the new "As Of Date" (a Join Load with no common fields will do it for you).

3. For each combination of "Date" and "As of Date", create a set of flags for any date buckets you want - "this year", "prior year", "this YTD", "Prior YTD" etc... Each flag gets a 1 if the Date belongs in the respective period compared to "As Of Date". If the dates don't belong in the period, the flag gets a 0 or null().

4. On the screen, let your users select "As Of Date" or any Year/Month attributes related to it (do not let them select the original Date).

5. In the chart expressions, use flags to limit your data:

this year sales = sum(Sales* ThisYearFlag)

good luck!

View previous year than the one selected on 8.2 Without Set Analysis

Fri, 04 Dec 2009 22:03:33 GMT

Sounds good Oleg. I will try this and verify once I complete it. Thanks for the response!