topic Re: Set analysis with if statement and getfieldselection in QlikView

Set analysis with if statement and getfieldselection

Tue, 18 Dec 2012 15:08:13 GMT

I apologize, I'm still learning set analysis. We have a field that is calculating the prior month units based on the year/month selected. Unfortunately this doesn't work when January is selected as the months are numeric so it sees month 1 and then subtracts 1 to get prior month, which doesn't work as there is no month zero. I need to modify the following formula to include if statements for both the year and the month. If the dteailmonth is >1 then use the detailmonth, else use detailmonth 12. And if the detailmonth=1 then the year needs to be detailyear-1.

Does anyone know how I can modify this to accomodate those if statements?

sum({$<detailmonth={"$(=GetFieldSelections(detailmonth)-1)"},detailyear = {"$(=GetFieldSelections(detailyear))"} >} units)

Thanks in advance!!~ Rebecca

Re: Set analysis with if statement and getfieldselection

MayilVahanan — Tue, 18 Dec 2012 15:26:13 GMT

Try like this

=Sum({<Date = {'>=$(=MonthStart(AddMonths(Max(Date),-1)))<=$(=MonthEnd(AddMonths(Max(Date),-1)))'},detailYear=,detailMonth=>}Sales)

Hope it helps

Re: Set analysis with if statement and getfieldselection

Tue, 18 Dec 2012 15:38:39 GMT

That doesn't quite work. I think it's because my data coming in is already parsed into months 1-12, and years. I don't actually have a Date field.

Re: Set analysis with if statement and getfieldselection

Tue, 18 Dec 2012 16:56:48 GMT

I ended up doing this and it seems to be working fine.

if(detailmonth>1,sum({$<detailmonth={"$(=GetFieldSelections(detailmonth)-1)"},detailyear = {"$(=GetFieldSelections(detailyear))"} >} units),
sum({$<detailmonth={12},detailyear = {"$(=GetFieldSelections(detailyear)-1)"} >} units))