topic Set analysis to exclude values from previous fiscal year ? in QlikView

Set analysis to exclude values from previous fiscal year ?

swarup_malli — Thu, 10 Nov 2011 22:09:07 GMT

the req is as follows :

the dates mentioned below

5/1/2007 to 4/30/2008 falls in fiscal year2007

5/1/2008 to 4/30/2009 falls in fiscal year2008

5/1/2008 to 4/30/2009 falls in fiscal year2009

5/1/2009 to 4/30/2010 falls in fiscal year2010

My requirement is :

There is a field called EndDate , which contains the dates on which the membership ended for a person .

Requirement is when i choose 2007, I only want records whose Enddates fall in the fiscal year 2007 , people whose membership ended in the previous fiscal year 2006 should be excluded.

I have written the below expression , I've been banging my head all day ;( to implement the above requiremnt into the expression below

SUM(

{$<INDACCOUNTNUMBER={"=COUNT({1<Techniques.TECHNIQUETYPE={'AF WLI'},Techniques.CAMPAIGNYEAR={'<=$(=vCampaignYearSelected)'}>}Techniques.TECHNIQUETYPE)>0

,CAMPAIGNYEAR={$(vCampaignYearSelected)}

LSIndiv.COMBINEDGIFT).

Regards

Swarup

Set analysis to exclude values from previous fiscal year ?

ToniKautto — Thu, 10 Nov 2011 22:20:32 GMT

Might be a bit tricky to evaluate without seing your datat model. Please attach a sample QVW, including the object where you are adding the expression.

Set analysis to exclude values from previous fiscal year ?

swarup_malli — Thu, 10 Nov 2011 22:49:52 GMT

Tony,

let me simplify the expression

SUM(LSIndiv.COMBINEDGIFT).

Set analysis to exclude values from previous fiscal year ?

Fri, 11 Nov 2011 04:51:49 GMT

Hello Swarup,

Check out indirect set analysis P and E. That might help.

If you post a file folks on the community can answer better.

Best Regards,
Vishal

Set analysis to exclude values from previous fiscal year ?

ToniKautto — Fri, 11 Nov 2011 08:11:56 GMT

One possible and simplified solution could be...

1. Add the fiscal year to your EndDate data table, e.g. EndDateFiscalYear

2. Use a variable to keep track of your selected calendar year,

e.g. vSelectedYear = GetFieldSelections(CalendarYear)

3. Use the variable to pick out your fiscal end dates; Sum( { $<EndDateFiscalYear={'$(vSelectedYear)'}>} LSIndiv.COMBINEDGIFT)

It is really hard and time consuming to assume what will work in your scenario, so a sample of the QVW including your datat model would really be needed in order to give a more exact answer.

Set analysis to exclude values from previous fiscal year ?

swarup_malli — Fri, 11 Nov 2011 15:05:20 GMT

Hi Tony and Vishal,

Thank you for your suggestion , unfortunately the data model i'm using is too big and complex to upload,

But thank you for trying to help

Regards

Swarup

Set analysis to exclude values from previous fiscal year ?

Fri, 11 Nov 2011 15:19:40 GMT

using yearstart and yearend might help you. You can use those to define the fiscal year, and then do something like enddate>=yearstart and enddate<=yearend.

Set analysis to exclude values from previous fiscal year ?

ToniKautto — Fri, 11 Nov 2011 20:10:37 GMT

What you could do in order to limit the QVW size, is to make as many selection as possible that still will present the problem you have encountered. Then you simply save the file as a new file and reduce it to only contain the possible values based on your current selection.

File > Reduce Data > Keep Possible Values

Set analysis to exclude values from previous fiscal year ?

swarup_malli — Mon, 14 Nov 2011 00:23:41 GMT

Found the solution in the following thread ..

http://community.qlik.com/message/164762#164762

By swuel

Thnx

M S