18 Replies Latest reply: May 5, 2016 9:29 AM by Sunny Talwar RSS

    Creating a new field dependent on several variables

    Filiberto Cacciari

      Hi everyone,

       

      what I'm looking for is to create a new field that is the sum of next three months and dependent on the value of some other fields...

      I try to explain it.

      First of all I don't know if that is possible or better to create it in script or in the front end, for me I think would be the same.

       

      I attach here the excel example of my database.

      We have "Date", "YearMonth" (field calculated from the Year and the Month of the field Date), "ITEM CODE", "Stock" (value), "CMV" (value) and at the end "CMV_3M" which is the field I want to create.

      As you can see in my excel, "CMV_3M" depends from different other fields: it should be the sum of the "CMV" of the next three months per each "ITEM CODE", but only if in that month "Stock" is different from zero, otherwise the value will be zero.

       

      Is that possible?

      Am I clear enough?

       

      stalwar1 swuehl gwassenaar

       

      Thank u all.

       

      Filiberto

        • Re: Creating a new field dependent on several variables
          Stefan Wühl

          You could start with a front end solution. Create a chart with dimensions Item Code and YearMonth and expressions:

           

          =Sum(Stock)

          =Sum(CMV)

          =If(Sum(Stock)>0, Rangesum(Below(Sum(CMV),1,3)),0)

           

          ITEM CODE YearMonth Sum(Stock) Sum(CMV) Sum(CMV_3M) If(Sum(Stock)>0, Rangesum(Below(Sum(CMV),1,3)),0)
          4138 7643 12624 0
          ABC2015032822011171117
          ABC20150443662013561356
          ABC201505201018311831
          ABC201506048700
          ABC201507085900
          ABC20150831048513341334
          ABC201509388667667667
          ABC20151038866700
          XYZ2015032822011301130
          XYZ20150443662013691369
          XYZ20150502300
          XYZ201506048700
          XYZ20150751085918191819
          XYZ20150831048513341334
          XYZ201509388667667667
          XYZ20151038866700
          • Re: Creating a new field dependent on several variables
            Sunny Talwar

            May be this, if you want to do this in the script:

             

            Table:

            LOAD Date,

                YearMonth,

                [ITEM CODE],

                Stock,

                CMV

            FROM

            [Filtro CMV per stock 0 con CMV_v2.xlsx]

            (ooxml, embedded labels, table is Foglio1);

             

            AggregatedTable:

            LOAD *,

              RangeSum(If([ITEM CODE] = Peek('ITEM CODE', -3), Peek('TotalCMV', -3)),

              If([ITEM CODE] = Peek('ITEM CODE', -2), Peek('TotalCMV', -2)),

              If([ITEM CODE] = Peek('ITEM CODE', -1), Peek('TotalCMV', -1))) as [LAST3MonCMV];

            LOAD YearMonth,

              [ITEM CODE],

              Sum(CMV) as TotalCMV

            Resident Table

            Group By [ITEM CODE], YearMonth

            Order By [ITEM CODE], YearMonth desc;

             

            Straight Table:

            Dimension: YearMonth

            Expression: =Sum(Aggr(If(Sum(Stock) > 0, LAST3MonCMV), YearMonth, [ITEM CODE]))


            Capture.PNG

              • Re: Creating a new field dependent on several variables
                Filiberto Cacciari

                Hi stalwar1,

                 

                I love your solution, but I see that now we have a "$Sin 1 Table". Could it be a problem in any case?

                 

                Thank you so much for your help.

                  • Re: Creating a new field dependent on several variables
                    Sunny Talwar

                    It should not be a problem, but there are ways to get rid of it using AutoNumber() function.

                      • Re: Creating a new field dependent on several variables
                        Filiberto Cacciari

                        stalwar1 Can you explain me better how is possible to use the AutoNumber() function in my script, please?

                         

                        Thank u.

                          • Re: Creating a new field dependent on several variables
                            Sunny Talwar

                            Here you go:

                             

                            Table:

                            LOAD Date,

                              AutoNumber(YearMonth&'|'&[ITEM CODE], 'Key') as Key,

                                YearMonth,

                                [ITEM CODE],

                                Stock,

                                CMV

                            FROM

                            [Filtro CMV per stock 0 con CMV_v2.xlsx]

                            (ooxml, embedded labels, table is Foglio1);

                             

                            AggregatedTable:

                            LOAD AutoNumber(YearMonth&'|'&[ITEM CODE], 'Key') as Key,

                              RangeSum(If([ITEM CODE] = Previous(Previous(Previous([ITEM CODE]))), Previous(Previous(Previous([TotalCMV])))),

                              If([ITEM CODE] = Previous(Previous([ITEM CODE])), Previous(Previous(TotalCMV))),

                              If([ITEM CODE] = Previous([ITEM CODE]), Previous(TotalCMV))) as [LAST3MonCMV];

                            LOAD YearMonth,

                              [ITEM CODE],

                              Sum(CMV) as TotalCMV

                            Resident Table

                            Group By YearMonth, [ITEM CODE]

                            Order By [ITEM CODE], YearMonth desc;

                             

                            Results are still be the same

                            Capture.PNG

                             

                            Data Model

                            Capture.PNG

                              • Re: Creating a new field dependent on several variables
                                Filiberto Cacciari

                                Hi stalwar1, in my real case I have to manage a lot of data.

                                Don't you think that using set analysis could optimize the visulization of my front end.

                                I tried this and seems that works in our example.

                                 

                                =Sum(Aggr(Sum({<Stock ={"<>0"} >} LAST3MonCMV), YearMonth, [ITEM CODE]))

                                 

                                What do you think about?

                                 

                                Thank you.

                                  • Re: Creating a new field dependent on several variables
                                    Sunny Talwar

                                    Set analysis surely improves performance. But I wasn't sure at what level do you want to perform the test. Sum(Sales) can be different from Sales if you have multiple rows for your dimensions. If you are going to have one sales amount for your dimension, then you can definitely go for it.

                                     

                                    Another option is like this:

                                    =Sum(Aggr(Sum({<Stock -={0}>} LAST3MonCMV), YearMonth, [ITEM CODE]))

                                     

                                    But in general, I agree that set analysis is better performing compared to the if statement.

                                     

                                    I guess in your real scenario, check if you get the same results or not. If you do, then go for set analysis

                                      • Re: Creating a new field dependent on several variables
                                        Filiberto Cacciari

                                        stalwar1

                                        Thank u for your explanation. I think that for my target it would be the same to use "Stock" or sum(Stock), so I can use Set analysis.

                                         

                                        Are there any difference between my solution =Sum(Aggr(Sum({<Stock ={"<>0"} >} LAST3MonCMV), YearMonth, [ITEM CODE])) and yours =Sum(Aggr(Sum({<Stock -={0}>} LAST3MonCMV), YearMonth, [ITEM CODE])).

                                         

                                        Both consider only the case where Stock is different from zero, right?

                                         

                                        One more thing, I tried to use Date (that's the field I got in my real scenario) to aggregate the fields in my script, but with this solution the front end doesn't work anymore...

                                         

                                        Table:

                                        LOAD Date,

                                         

                                          AutoNumber(Date&'|'&[ITEM CODE], 'Key') as Key,

                                         

                                            YearMonth,

                                         

                                            [ITEM CODE],

                                         

                                            Stock,

                                         

                                            CMV

                                        FROM

                                        [C:\Users\fcacciari\Desktop\KPMG\00_Progetti\Unieuro\02_Working Capital\QlikView\Ricevuti\Mazzotti_Check\Nuove Analisi\DII con CMV=0\Filtro CMV per stock 0 con CMV_v2.xlsx]

                                        (ooxml, embedded labels, table is Foglio1);

                                         

                                        AggregatedTable:

                                         

                                        LOAD AutoNumber(Date&'|'&[ITEM CODE], 'Key') as Key,

                                         

                                          RangeSum(If([ITEM CODE] = Previous(Previous(Previous([ITEM CODE]))), Previous(Previous(Previous([TotalCMV])))),

                                         

                                          If([ITEM CODE] = Previous(Previous([ITEM CODE])), Previous(Previous(TotalCMV))),

                                         

                                          If([ITEM CODE] = Previous([ITEM CODE]), Previous(TotalCMV))) as [LAST3MonCMV];

                                         

                                        LOAD Date,

                                         

                                          [ITEM CODE],

                                         

                                          Sum(CMV) as TotalCMV

                                         

                                        Resident Table

                                         

                                        Group By Date,[ITEM CODE]

                                         

                                        Order By [ITEM CODE], Date desc;

                                         

                                        Can you help me, please?