topic Re: Set Analysis - excluding values with condition sum in QlikView

Set Analysis - excluding values with condition sum

Anonymous — Thu, 13 Oct 2016 12:55:12 GMT

Goodmorning everyone,

Can you help me on this Set Analysis.

sum({<FlagLYTLM={1},Year=,YearID=,YearMonth=,EconomicYear=,CLASSE_CLIENTE={Marginali}, CLI_COD -= {"=sum({<FlagCYTLM={1},Year=,YearID=,YearMonth=,EconomicYear=>}$(varFatturato))<=0"}>} $(varFatturato))

My goal is to exclude from the initial sum (sum({<FlagLYTLM={1},Year=,YearID=,YearMonth=,EconomicYear=,CLASSE_CLIENTE={Marginali})....) all customers (CLI_COD) that have satisfied the second sum.

(sum({<FlagCYTLM={1},Year=,YearID=,YearMonth=,EconomicYear=>}$(varFatturato))<=0"})

these flags identify different years of data sets

FlagLYTLM = Last Year

FlagCYTLM = Current Year.

The formula above does not work I

Thank you all

Re: Set Analysis - excluding values with condition sum

sunny_talwar — Thu, 13 Oct 2016 13:00:06 GMT

The expression looks good to me (syntax wise). Would you be able to share a sample to look at this in dept?

Re: Set Analysis - excluding values with condition sum

Anonymous — Thu, 13 Oct 2016 13:28:54 GMT

I attach files, reduced application and Excel files containing the result.

Thank you

Manuel

Re: Set Analysis - excluding values with condition sum

sunny_talwar — Thu, 13 Oct 2016 14:00:29 GMT

Which tab am I looking at in your attached qvw? Also what is column 2014 and 2013 in your Excel file? Those are the numbers you are looking to see in your application?

Re: Set Analysis - excluding values with condition sum

Anonymous — Thu, 13 Oct 2016 14:08:28 GMT

The column in the application that should give the result is NETTO YTD 2013

In the Excel file the column is 2013. It must add the values of the 2013 column that have 0 in the 2014 column.

Re: Set Analysis - excluding values with condition sum

sunny_talwar — Thu, 13 Oct 2016 15:05:06 GMT

Here try this:

Sum(Aggr(If(

Sum({<FlagCYTLM={1},Year=,YearID=,YearMonth=,EconomicYear=,CLASSE_CLIENTE={Marginali}>} $(varFatturato)) = 0,

Sum({<FlagLYTLM={1},Year=,YearID=,YearMonth=,EconomicYear=,CLASSE_CLIENTE={Marginali}>} $(varFatturato))), CLI_COD, CLASSE_CLIENTE))

Re: Set Analysis - excluding values with condition sum

Anonymous — Thu, 13 Oct 2016 15:15:27 GMT

thank you very much works perfectly!!!

Manuel