Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Set Analysis Ignore Dimension

Hello

i have the follow requirement.

Formula: Previous Month + current Month - Previous Month when Fecha Liquidacion = current Month

i used the method AsOfMonth to calc the previous month. Works fine. Then i used the follow set analysis:

RangeSum(

          sum({$<

                  Tiempo={'Pasado'},  //Previous Month

                  bEsPender={1}

               >} Contrato), 

             

         

          Sum({$<

                  Tiempo={'Actual'},  //current Month

                  bEsPender = {1}

               >} Contrato),

         

          (sum({$<

                   Tiempo={'Pasado'}, //Previous month when Fecha liquidacion = AsOfYear

                   bEsPender={1},

                   [Fecha Liquidacion]= {'>=$(=MonthStart(Max(AsOfYear)))<=$(=MonthEnd(Max(AsOfYear)))'}

                >} Contrato)*-1)

          )

if i selected a AsOfYear works fine but if selected two ore more not working. The set analysis always take the max AsOfYear

1.JPG

3.JPG

2.JPG

Thanks

1 Solution

Accepted Solutions

Check the attached

RangeSum(

       

         

          sum({$<

                  Tiempo={'Pasado'},

                  bEsPender={1}

              >} Contrato), 

             

         

          Sum({$<

                  Tiempo={'Actual'},

                  bEsPender = {1}

              >} Contrato),

               

          (Sum({$<Tiempo={'Pasado'}, bEsPender={1}>}Aggr(If([Fecha Liquidacion] >= MonthStart(Max(AsOfYear)) and [Fecha Liquidacion] <= MonthEnd(Max(AsOfYear)), Contrato), AsOfYear, [Fecha Liquidacion], %cont_id))*-1)

        )

Capture.PNG

View solution in original post

6 Replies

Would you be able to share a sample where we might be able to play around with this?

Thanks,

Sunny

Contributor III
Contributor III

Thanks Sunny

What do you expect to see in your chart when you have two years selected?

Contributor III
Contributor III

if i selected two month the set analysis take the max AsOfYear, i need take the value of the dimension

1.JPG2.JPG

in the second image ene. 2017 should be 160

Check the attached

RangeSum(

       

         

          sum({$<

                  Tiempo={'Pasado'},

                  bEsPender={1}

              >} Contrato), 

             

         

          Sum({$<

                  Tiempo={'Actual'},

                  bEsPender = {1}

              >} Contrato),

               

          (Sum({$<Tiempo={'Pasado'}, bEsPender={1}>}Aggr(If([Fecha Liquidacion] >= MonthStart(Max(AsOfYear)) and [Fecha Liquidacion] <= MonthEnd(Max(AsOfYear)), Contrato), AsOfYear, [Fecha Liquidacion], %cont_id))*-1)

        )

Capture.PNG

View solution in original post

Contributor III
Contributor III

thank you very much for your help.