12 Replies Latest reply: Apr 11, 2011 11:28 AM by Gerhard Laubscher RSS

    Working with dates

    Gerhard Laubscher

      Hi,

      I have a field (currently displayed as a table box) with dates that accounts were opened. There are about 120,000 accounts.

      I would like to show a summary box (or anything) where I can break down the data a bit more. I would for instance like to show how many accounts were opened during the last week/month/year.

      Could anyone set me on the right direction? Also, is there anyway to display the 'account opend dates' in broken down list boxes, i.e. one list box showing Years, another showing Months, another showing Days. So that if I click on 2011 I will see all accounts opened in 2011, and the months Jan, Feb, March, April will be shown as possible values in the other box. Is this possible?

        • Working with dates
          Sushil Kumar

          yes it is possible use MONTH(),YEAR(), and DAY() function and make new fields....

          apply the above function on the Account Open Date field..

          i think it would help you

          • Working with dates
            Deepak Vadithala

            Please use the DATE and TIME expressions as derived columns.

            Example:

            Month([Account Open Date])

            Year([Account Open Date])

            MonthName([Account Open Date])

            I hope this helps you.

            Cheers - DV

             

              • Working with dates
                Gerhard Laubscher
                Hi,
                • Working with dates
                  Gerhard Laubscher

                  Hi,

                  Where do I create derived columns? Can I do this in qlikview, or do I have to change this in the script, i.e.

                  Load

                  Month([Account Open Date]) as ??????

                  Please explain to me in detail if possible.

                  Much appreciated.

                  G

                    • Working with dates
                      Deepak Vadithala

                      Hi,

                      If you are planning to use it as Time Dimension then please load it in the Load Script. However, you can also use these functions in both Expressions and Load Script.

                      If you are using in Load Script Alias the column names and use these functions within your table. And if you wanted to use them as expression you can directly use the [Account Open Date] field name with the DATE and TIME Functions.

                      I hope this helps...

                      Let me know if you need more.

                      Cheers - DV

                        • Working with dates
                          Deepak Vadithala

                          I am glad it worked for you. Sorry I could not reply back to your old question 3 days back. I hope someone might have helped you.

                          Cheers - DV

                          • Working with dates
                            Gerhard Laubscher

                            Thanks deepak it worked great - I was just having trouble because my script also contained date format changes, but I got it to work.

                            Now I have 3 boxes, "account open day", "account open month" and "account open year". When I select 2011 it works perfectly, and my overview sheet will then show me the number selected.

                            But I would like to have a permanent box on my "overview" sheet, that shows "the number of accounts opened this month". So it must automatically measure from the 1st of the current month, and every day when I update my script with the new data, it must also include the new day's new accounts. And on the first of the month it must again read "0".

                            Possible? Thanks for all your help.

                            Regards,

                            G

                              • Working with dates
                                Deepak Vadithala

                                Very interesting! I am sure this is certainly possible. However, I would recommend you to start a new post. I will work on this one sometime today, meanwhile other users can pour in their ideas.

                                Just to give some idea... you need to consider two things to get this working.

                                1. Assocative Query Logic should not influence for this expression or calculation. I mean irrespective of whichever month you select it should still show the Current Month Till Date (C-MTD). You can achieve this by using variable and set analysis.

                                2. The expression should be based on the Date Field to calculate the C-MTD score. I mean whenever you load the data it should automatically evaluate the expression and add up the new account (number of accounts).

                                I hope I had not confused you..

                                But this is certainly possible and I had done this kinda stuff in the past.

                                Cheers - DV

                                  • Working with dates
                                    Gerhard Laubscher

                                    I have (almost) figured it out. I will start a new post if I can't figure it out 100%.

                                    So far I have created a straight table with the following expressions:

                                    For the accounts opened this year - Count ({1<[Account open year] = {2011}>}[Account open date])

                                    For the accounts opened this month - Count ({1<[Account open month] = {Apr}, [Account open year] = {2011}>}[Account open date])

                                    That works beautifully but I will have to change the expression every month. I just need to replace 2011 and April with "current year" or "current month"? Can you help me with that or should I just start a new post?

                                    Thanks

                                      • Working with dates
                                        Deepak Vadithala

                                        Excellent! Good progress...

                                        I would recommend using variables in your experssion. I mean define two variables like

                                        vCurrentYear = Max(Year([Account open year]))

                                        vCurrentMonth = Max(Month([Account open year]))

                                        Then please use them in your expressions... something like this..

                                        Count ({1<Month(Date([Account open year])) = {$(=($(vCurrentMonth)))}>}[Account open date])

                                        Count ({1<Year(Date([Account open year])) = {$(=($(vCurrentYear)))}>}[Account open date])

                                        I hope this helps...

                                        Cheers - DV

                                          • Working with dates
                                            Deepak Vadithala

                                            With the above solution you dont have to change the month & year everytime you load the new data. Because your variables evaluate the Current month based on the function and then return a value in your original expression.

                                            Good luck!

                                            Cheers - DV

                                              • Working with dates
                                                Gerhard Laubscher

                                                Hey DV,

                                                I tried my hardest to make sense of that - but no luck. Would you mind explaining it to me step by step?

                                                I go to Settings - Variable Overview, then I add one called "vCurrentYear" with definition Max(Year([Account open year]))

                                                And then a second one "vCurrentMonth = Max(Month([Account open year])). Should this not be Account open month??

                                                After that I am lost, I have no idea how those expressions work. When I change my current expressions to the ones you gave me I get "Bad fieldname: Year, Date" at the top of the expression box...

                                                This is my load statement:

                                                LOAD [Product Name],
                                                [ID number],
                                                [Account no],
                                                [Card no],
                                                DATE(DATE#( [Account open date], 'DD-MMM-YY')) as [Account open date],
                                                month(DATE(DATE#( [Account open date], 'DD-MMM-YY'))) as [Account open month],
                                                year(DATE(DATE#( [Account open date], 'DD-MMM-YY'))) as [Account open year],
                                                day(DATE(DATE#( [Account open date], 'DD-MMM-YY'))) as [Account open day],
                                                [Account status code],
                                                [Charge off Status],
                                                DATE(DATE#( [Account status change date], 'DD-MMM-YY')) as [Account status change date], etc.