4 Replies Latest reply: Sep 27, 2018 2:09 PM by Miriam Ramos RSS

    Prior Year Sales Expression Not Working

    Miriam Ramos

      I'm using QBAR (Quickbooks Advanced Reporting) which is based on Qlikview.  I have a formula for current year sales calculation which is working:

      sum({<$(vExprSales)>}if(Not IsNULL([Item.Item Type]) and  Not IsNull([Item.Full Name]) and Not IsNull([Transactions.Txn Type]),

      if(Match([Transactions.Classified Account Type], 'Assest', 'Expense'), -[Transactions.Amount With Sign] ,

      if( not Match([Transactions.Classified Account Type], 'Assets', 'Expense'),[Transactions.Amount With Sign], 0))))

       

      Now I need to edit it to show prior year data.  The current year formula shows year, quarter, month or date using the "Objects Displayed in Container" of LB02, LB01, LB03 and LB14 respectively and updates results just by clicking on the different transaction date in the selection window (Called container properties [Transaction Date]).  Would like for the same time frames to update prior year when I make the selection for current year.

       

      Any help would be greatly appreciated!

        • Re: Prior Year Sales Expression Not Working
          Shahbaz Khan Mohammed

          where is your Year statement in the expression?

          How many Years you have in your filter?

          you can use

          Year={$(= (Year)-1)} in set analysis or Year = {$(=Max(Year)-1)}>}

          • Re: Prior Year Sales Expression Not Working
            Miriam Ramos

            Not sure how it knows the year but when I click on the different transaction date labels, it updates correctly for year, quarter, month, etc (in what is labeled the "current" year regardless of what year I select).

            I am just trying to compare one year back from the selected year (is that what you mean by years in filter?) so if my "current" is 2016, I would want my prior to be 2015 and then within those selections if I limited to Q1 would want both "current" and "prior" years to isolate Q1 data.

              • Re: Prior Year Sales Expression Not Working
                Miriam Ramos

                So I got it working for year.  Now I just need to get it working if I select quarter or month.  Here's the modification that made the year calculation work:

                sum({<$(vExprSales),$(vExprPreviousNYears(1))>}

                if(Not IsNULL([Item.Item Type]) and  Not IsNull([Item.Full Name]) and Not IsNull([Transactions.Txn Type]),

                if(Match([Transactions.Classified Account Type], 'Assets', 'Expense'), -[Transactions.Amount With Sign] ,

                if( not Match([Transactions.Classified Account Type], 'Assets', 'Expense'),[Transactions.Amount With Sign], 0))))

                 

                I've tried copying the entire expression and editing for

                sum({<$(vExprSales),$(vExprPreviousNQuarters(5))>}

                if(Not IsNULL([Item.Item Type]) and  Not IsNull([Item.Full Name]) and Not IsNull([Transactions.Txn Type]),

                if(Match([Transactions.Classified Account Type], 'Assets', 'Expense'), -[Transactions.Amount With Sign] ,

                if( not Match([Transactions.Classified Account Type], 'Assets', 'Expense'),[Transactions.Amount With Sign], 0))))

                and

                sum({<$(vExprSales),$(vExprPreviousNMonths(13))>}

                if(Not IsNULL([Item.Item Type]) and  Not IsNull([Item.Full Name]) and Not IsNull([Transactions.Txn Type]),

                if(Match([Transactions.Classified Account Type], 'Assets', 'Expense'), -[Transactions.Amount With Sign] ,

                if( not Match([Transactions.Classified Account Type], 'Assets', 'Expense'),[Transactions.Amount With Sign], 0))))

                 

                I've also tried variations of

                sum({<$(vExprSales),$(vExprPreviousNYears(1)),$(vExprPreviousNQuarters(5),$(vExprPreviousNMonths(13)>}

                if(Not IsNULL([Item.Item Type]) and  Not IsNull([Item.Full Name]) and Not IsNull([Transactions.Txn Type]),

                if(Match([Transactions.Classified Account Type], 'Assets', 'Expense'), -[Transactions.Amount With Sign] ,

                if( not Match([Transactions.Classified Account Type], 'Assets', 'Expense'),[Transactions.Amount With Sign], 0))))

                but without success.