9 Replies Latest reply: Feb 1, 2016 3:58 AM by pavan ch RSS

    Creating Dynamic Master Items

      Hi Guys,

       

      I have a question:

       

      I have uploaded in my app a sales record with some attributes as day, product sold, quantity, price….(all columns).

       

      Day       Product             quantity   price
      1 Nov   Red shoes           2             7$
      1 Nov   Blue shoes          1             2$

      ...

      2 Nov

      3 Nov

      ....


      I have created a master item called months where I add all the days of the month,

      And a master item called week, where I add the weeks.

       

      Now, what I want to do is to create three different measures

      1) Last 30 days

      2) Last 7 days

      3) Last 3 days

      Basically what I would like to do is to put these three measures in a table as column, in a way that I can see something like

       

      PRODUCT                              Last 30 days          last 7 days          last 3 days


      Black shoes sold                              25                         6                         2

      Red shoes sold                                95                         12                        7

      ............


      And so on.

      The goal would be to create “Auto updating master measures”

      This means that every day, when I replace the existing doc with an updated version, my app should be able to scan all the days in the “days” column, find the last 30, last 7 and last 3, and update the master measures.

       

      The product column is then calculated including the master measure in the calculation and it’s automatically updated.

       

      Is this possible?

      Thank you in advance

        • Re: Creating Dynamic Master Items
          Casey Burrow

          See these posts about rolling period calculations:

          Set Analysis for Rolling Periods

          Re: Set Analysis Rolling Period

          • Re: Creating Dynamic Master Items
            Mark Little

            Hi,

             

            Something like the below in set analysis

             

            SUM({<Day={'<=Today(),>=Today()-3}>}quantity)

             

            You have to be careful of the date formats though.

             

            Mark

              • Re: Creating Dynamic Master Items

                Hi Mark,
                Thanks for your reply,

                the thing is that I export a table with the first column like

                 

                Day

                 

                1/1/15

                1/2/15

                1/3/15

                1/4/15

                1/5/15

                 

                so I have first to say to the app what is "today", which should be the "highest" value of the column

                 

                From it, creating a dynamic item called "Last 7 days" that can basically fill a table like

                 

                Day
                ....
                ...
                ...
                ...

                ...

                ...

                today


                Same for last 3 days

                Day

                ....

                ...

                ...

                Today

                 

                You see what I mean?

                 

                Best Regards,

                 

                P

              • Re: Creating Dynamic Master Items
                Srikanth P

                You can create expression for each measure in straight table like below:

                 

                Assume you have date field in your Master Items table

                 

                Last 30 Days : Sum({DateField={">=$(=Max(DateField)-30)<=$(=Max(DateField))"}}quantity)

                 

                Last 7 Days : Sum({DateField={">=$(=Max(DateField)-7)<=$(=Max(DateField))"}}quantity)

                 

                Last 3 Days : Sum({DateField={">=$(=Max(DateField)-3)<=$(=Max(DateField))"}}quantity)

                  • Re: Creating Dynamic Master Items

                    Hi,

                     

                    Thanks so much, I feel that I am close to what I want but it still doesn't work.

                     

                    I'd like to point this out

                     

                    All the products, the spend and all the other attributes are already aggregated by day.
                    It means that I am already able to plot a table where all the data are aggregated (with sum)

                     

                    I can already have smth like

                     

                    Date             Product            Quantity        Price per unit    Total Price         Shop
                    1 Nov            Blue shoes        4                 30                        120               London

                    1 Nov            Red shoes         5                 30                        150              Chelsea

                    2 Nov            Blue shoes        2                 30                        60              London

                    ............

                    ...........

                    .........

                    .........

                     

                    what I want to do is only to manipulate the date column

                     

                    so I want to be able to plot 3 tables at the same time

                     

                    last 3

                    last 7

                    last 30

                     

                    so I can have in real time 3 separate tables with a manipulate date column, and the rest (should) come automatically.

                     

                    Hope it's clear

                     

                    (I am quite new with Qlik so sorry if it may look a very basic question)

                     

                    Best

                     

                    P

                    • Re: Creating Dynamic Master Items
                      pavan ch

                      Hi,

                       

                      I would like to show only data for last 7 days of last week, as per my requirement my weekstarts from Friday and ends on Thursday, so currently for ex: today is Monday so I want to show the data from Friday 22nd January to Thursday January 28th with the measure.

                       

                      My expression will be:

                      =Sum({Calculation} Sales)

                       

                      Please find the attached screenshot for the same.

                       

                      Please help on this, thanks