Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis with if statement and getfieldselection

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

1 Solution

Accepted Solutions
Not applicable
Author

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))

View solution in original post

3 Replies
MayilVahanan

HI

Try like this

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

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Not applicable
Author

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.

Not applicable
Author

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))