QlikView - First Month Data and Actual Month

[Anonymous]

Hi,

Continuing with this threads:

QlikView - First Value with Data, Current and Previous Month Value and Evolution

I want to get.

- in jan-2017 the value of first month with data, for the codes that have value in jan-2017.

- in fev-2017 the value of first month with data, for the codes that have value in fev-2017.

Example

This is my data table:

And this is my expected result:

In  column First Month With Data I want:

- in jan-2017:

    (Value for Code 310 in jan-2017 + Value for code 1066 in jan-2017) / 2

    Only this two codes have value in jan-2017

- in fev-2017:

    (Value for Code 310 in jan-2017 + Value for code 1066 in jan-2017 + Value for code 310 in fev-2017) / 3

   Three codes have value in fev-2017

I want to sum up the value of the first month of occurence for each code, and to its average

In column Current Month I want:

- in jan-2017:

  (Value for Code 310 in jan-2017 + Value for code 1066 in jan-2017) / 2

- in fev-2017:

  (Value for Code 310 in fev-2017 + Value for code 1066 in fev-2017 + Value for code 310 in fev-2017) / 3

I've attached an app sample.

Thanks in advanced,

Sílvia

[sunny_talwar · Accepted solution]

Try this

if([Average of Current Month],

NUM(

  avg(

  Aggr(

  FirstSortedValue({<Value = {'*'},Active={"1"},Metric = {"Sales"},Flag = {'CR'}>}

  TOTAL <Code>

  if(Value,

  Aggr(

  avg({<Metric={"Sales"}, Active={"1"},Flag = {'CR'}>}  Value)

  , Code, MonthYear)

  )

  , Aggr(only(MonthYear), Code, MonthYear)

  )

  , Code, MonthYear)

  )

,'#,## %')

)

[Anonymous]

Hi sunny,

i use this script , but sometimes value is 0. with the condition "if(Value..."  i got diferent values. i need to changed to

"if(Value>=0..." .

Is the condition even necessary?

thx

Ricardo