Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Problem with IF

Hello everybody!

I have this expression to have the accumulated values of 12months.

Rangesum (above(TOTAL SUM({1<_Año={$(V_ActualYear)},_MesNum={'<=$(V_Max_Month)'},Month=>}Sales),0,RowNo(TOTAL)))

+

IF(MonthNum=1,

SUM({1<Year={'$(V_LastYear)'},MonthNum={'>1'},Month=>}TOTAL Sales)

    ,IF(_MesNum=2,

    SUM({1<Year={'$(V_LastYear)'},MonthNum={'>2'},Month=>}TOTAL Sales)

        ,IF(_MesNum=3,

        SUM({1<Year={'$(V_LastYear)'},MonthNum={'>3'},Month=>}TOTAL Sales)

            ,IF(_MesNum=4,

            SUM({1<Year={'$(V_LastYear)'},MonthNum={'>4'},Month=>}TOTAL Sales)

                ,IF(_MesNum=5,

                SUM({1<Year={'$(V_LastYear)'},MonthNum={'>5'},Month=>}TOTAL Sales)

                    ,IF(_MesNum=6,

                    SUM({1<Year={'$(V_LastYear)'},MonthNum={'>6'},Month=>}TOTAL Sales)

                        ,IF(_MesNum=7,

                        SUM({1<Year={'$(V_LastYear)'},MonthNum={'>7'},Month=>}TOTAL Sales)

                            ,IF(_MesNum=8,

                            SUM({1<Year={'$(V_LastYear)'},MonthNum={'>8'},Month=>}TOTAL Sales)

                                ,IF(_MesNum=9,

                                SUM({1<Year={'$(V_LastYear)'},MonthNum={'>9'},Month=>}TOTAL Sales)

                                    ,IF(_MesNum=10,

                                    SUM({1<Year={'$(V_LastYear'},MonthNum={'>10'},Month=>}TOTAL Sales)

                                        ,IF(_MesNum=11,

                                        SUM({1<Year={'$(V_LastYear)'},MonthNum={'>11'},Month=>}TOTAL Sales)

                                            ,IF(_MesNum=12,

                                            SUM({1<Year={'$(V_LastYear)'},MonthNum={'>12'},Month=>}TOTAL Sales)

                                                ,0)

                                            )

                                        )

                                    )

                                )

                            )

                        )

                    )

                )

           )

       )   

    )

The expression works for the results, but I´m trying to do it independent of the selection of "Months".

Thats why I have in the set analysis: Month=.

When I select a Month all my expression changes, but as I see it it shouldn´t.

Maybe you know what´s happening and how can I solve this problem? ( I´ve already tried with Alternate States but I would prefer another solution)

Thank you very much!

2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

I think it would be useful if you provide a small example of your issue.

- Marcus

Highlighted
Contributor III
Contributor III

I finally managed to solve it using the Rowno() instead of the dimension.

A year has always 12 months, so the first line of the table will always be jenuary, the second one february etc...

Rangesum (above(TOTAL SUM({1<_Año={$(V_ActualYear)},_MesNum={'<=$(V_Max_Month)'},Month=>}Sales),0,RowNo(TOTAL)))

+

IF(Rowno()=1,

SUM({1<Year={'$(V_LastYear)'},MonthNum={'>1'},Month=>}TOTAL Sales)

    ,IF(Rowno()=2,

    SUM({1<Year={'$(V_LastYear)'},MonthNum={'>2'},Month=>}TOTAL Sales)

        ,IF(Rowno()=3,

        SUM({1<Year={'$(V_LastYear)'},MonthNum={'>3'},Month=>}TOTAL Sales)

            ,IF(Rowno()=4,

            SUM({1<Year={'$(V_LastYear)'},MonthNum={'>4'},Month=>}TOTAL Sales)

                ,IF(Rowno()=5,

                SUM({1<Year={'$(V_LastYear)'},MonthNum={'>5'},Month=>}TOTAL Sales)

                    ,IF(Rowno()=6,

                    SUM({1<Year={'$(V_LastYear)'},MonthNum={'>6'},Month=>}TOTAL Sales)

                        ,IF(Rowno()=7,

                        SUM({1<Year={'$(V_LastYear)'},MonthNum={'>7'},Month=>}TOTAL Sales)

                            ,IF(Rowno()=8,

                            SUM({1<Year={'$(V_LastYear)'},MonthNum={'>8'},Month=>}TOTAL Sales)

                                ,IF(Rowno()=9,

                                SUM({1<Year={'$(V_LastYear)'},MonthNum={'>9'},Month=>}TOTAL Sales)

                                    ,IF(Rowno()=10,

                                    SUM({1<Year={'$(V_LastYear'},MonthNum={'>10'},Month=>}TOTAL Sales)

                                        ,IF(Rowno()=11,

                                        SUM({1<Year={'$(V_LastYear)'},MonthNum={'>11'},Month=>}TOTAL Sales)

                                            ,IF(Rowno()=12,

                                            SUM({1<Year={'$(V_LastYear)'},MonthNum={'>12'},Month=>}TOTAL Sales)

                                                ,0)

                                            )

                                        )

                                    )

                                )

                            )

                        )

                    )

                )

           )

       )   

    )