9 Replies Latest reply: May 11, 2012 6:32 AM by Ahmed Ali Shiraz RSS

    Previous Transaction Date Data

    Ahmed Ali Shiraz

      Dear All,

       

      I want to create a bar chart which would show a variance of target vs last day, in it i am facing a problem to get balance of previous transactional date.

       

      The scenario is if i am standing on 3 january 2012, showing me some balance and 2 and 1 januray were holidays so they did not have any balance, so i should be having the balance of 31 december 2011 as it has the balance data, but i dont know the experssion for how to get the balance of 31 december 2011. The expression should get the previous date data and previous date could be any previous date having balance.

       

      I need urgent help on this matter.

      Thanks

        • Previous Transaction Date Data
          Shivarama krishna K

          Hi ,

           

          Can u attach any sample data file.

           

           

          Shivaram

            • Re: Previous Transaction Date Data
              Ahmed Ali Shiraz

              attached please find a sample file I have created for previous date selection.

               

              You could see 2 text objects, 1 at center is for calculating max dates and the second below is calculating the previous date. so if I choose 3 Jan 2011, than this text object would show me the previous date which is 31 DEC 2010. if I choose 1 Feb 2011, the text object would show me 3 Jan 2011.

               

              Urgent help would be appreciated.

              Thanks

                • Re: Previous Transaction Date Data
                  Deepak Vadithala

                  Hi Ahmed,

                   

                  There are various methods to get this working. However, this is the most straight forward logic to get this working. Here is what you need :

                   

                  =MaxString({<WODate = {"<$(=Date(MaxString(WODate), 'MM/DD/YYYY'))"}>} Sales)

                   

                  Explanation :

                   

                  Step 1 : Date(MaxString(WODate), 'MM/DD/YYYY')

                  This expression calculates Max Date from WoDate field and formats it by using Date() function.

                   

                  Step 2 : MaxString({<WODate = {"<$(=Date(MaxString(WODate), 'MM/DD/YYYY'))"}>} Sales)

                  This expression calculates Max Date less than the Date from Step 1. Which means previous Date in your example. And then we are extracting the Max sales value as final output.

                   

                  I hope this makes sense!

                   

                  Good luck!

                   

                  Cheers - DV

                  http://QlikShare.Com

              • Re: Previous Transaction Date Data
                Ahmed Ali Shiraz

                Thanks a lot D V your expression of maxstring works like a charm and also firstsortedvalue was another option for it.

                 

                Now I am having another issue now to find the monthly Transactional period end Balance;

                I need to show balance on month Transactional period end, so how to showit, because I tried to show monthly balance with every month’s end datebalance. Now the scenario is I want to show a line chart, which would berunning on month dimension and it shows the balance of each months end date.Like

                 

                Month: MAX(Date)of that month: Balance

                Jan 31 2000

                Feb 28 1500

                Mar 30 1400

                April 21 1800

                 

                Your help me in this issue would be highly appreciated.

                  • Re: Previous Transaction Date Data
                    Deepak Vadithala

                    Hi Ahmed,

                     

                    You are welcome. I would recommend you to use the FirstSortedValue solution instead of the first workaround.

                     

                    I'll look into above request and I'll get back to you sometime today.

                     

                    Cheers - DV

                    • Re: Previous Transaction Date Data
                      Deepak Vadithala

                      Ahmed - Is it okay if I get back to you tomorrow? I have few things to finish tonight. Sorry!

                       

                      Cheers ,

                      DV (Deepak Vadithala)

                      www.QlikShare.com

                      • Re: Previous Transaction Date Data
                        Deepak Vadithala

                        Ahmed - Good morning. Not sure if I have understood your previous question. Do you want to show monthly closing balance for each month? I mean your dimension would be Month and your expression would be MaxString(Sales). If you want something else then please can you explain in detail? Probably with some example like your original question.

                         

                        Thanks - DV

                          • Re: Previous Transaction Date Data
                            Ahmed Ali Shiraz

                            ok, i think you didnt get what i mean. What i mean is if i have a data like this;

                             

                            Date1, Sales

                                12/31/2010 12:00:00 AM, 43

                                01/03/2011 12:00:00 AM, 101

                                01/28/2011 12:00:00 AM, 98

                                02/01/2011 12:00:00 AM, 102

                                02/26/2011 12:00:00 AM, 76

                                03/01/2011 12:00:00 AM, 103

                                04/01/2011 12:00:00 AM, 104

                                05/01/2011 12:00:00 AM, 105

                                06/01/2011 12:00:00 AM, 126

                                01/21/2011 12:00:00 AM, 111

                                02/23/2011 12:00:00 AM, 112

                                03/22/2011 12:00:00 AM, 113

                                04/25/2011 12:00:00 AM, 114

                                05/26/2011 12:00:00 AM, 115

                                06/27/2011 12:00:00 AM, 116

                             

                            and i need to know the monthly period end balance of this data. monthly period end means, the last working date balance of every month.

                            which is, if i set month in deminsion than

                            jan 98

                            feb 76

                            mar 113

                            apr 114

                            may 115

                            jun 116

                             

                            attached please find a sample qvw on this data. Thanks