1 Reply Latest reply: Aug 13, 2017 10:37 AM by Sunny Talwar RSS

    get results of 2016 while selecting 2017

    Ali Hijazi

      @Hello got the following expression:

      =sum(aggr(

      alt(

      aggr(sum(DISTINCT{<trx_type={'sales_forecast'},price_category={'customer_price'}>} if(trx_date>= from_period and trx_date <= to_period, price_kg)),account_company_key,MONTH_YEAR,customer_key,product_key)

      ,

      aggr(sum(DISTINCT{<trx_type={'sales_forecast'},price_category={'customer_group_price'}>} if(trx_date>= from_period and trx_date <= to_period, price_kg)),account_company_key,MONTH_YEAR,customer_key,product_key)

      ,

      aggr(DISTINCT sum({<trx_type={'sales_forecast'},price_category={'default_product_price'}>} if(trx_date>= from_period and trx_date <= to_period, price_kg)),account_company_key,MONTH_YEAR,customer_key,product_key)

      )

      ,account_company_key,MONTH_YEAR,customer_key,product_key))

       

      it gives correct result when I select the year 2017 or 2016

      Now I want to create the alternative expression that shows the result of 2016 when user selects 2017

       

      check attached qvw file;

      the above expression is used in the text object captioned as "Current Selection" and the attempt to get the result for 2016 is in the other text object but is giving zero unless I don't select a YEAR

       

      please help gwassenaar tresesco mbaeyens stalwar1

        • Re: get results of 2016 while selecting 2017
          Sunny Talwar

          May be this

           

          =sum({<YEAR={$(vLY)}>}aggr(

          alt(

          Only({<YEAR={$(vLY)}>}aggr(sum(DISTINCT{<trx_type={'sales_forecast'},price_category={'customer_price'}, YEAR={$(vLY)}>} if(trx_date>= from_period and trx_date <= to_period, price_kg)),account_company_key,MONTH_YEAR,customer_key,product_key))

          ,

          Only({<YEAR={$(vLY)}>}aggr(sum(DISTINCT{<trx_type={'sales_forecast'},price_category={'customer_group_price'}, YEAR={$(vLY)}>} if(trx_date>= from_period and trx_date <= to_period, price_kg)),account_company_key,MONTH_YEAR,customer_key,product_key))

          ,

          Only({<YEAR={$(vLY)}>}aggr(sum(DISTINCT{<trx_type={'sales_forecast'},price_category={'default_product_price'}, YEAR={$(vLY)}>} if(trx_date>= from_period and trx_date <= to_period, price_kg)),account_company_key,MONTH_YEAR,customer_key,product_key))

          )

          ,account_company_key,MONTH_YEAR,customer_key,product_key))