Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
)
,'#,## %')
)
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)
)
,'#,## %')
)
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