Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there!
Need your help so much with issue: I need to calculate Average by Dates with formula: 1-fact/fact2.
I've managed with Text Object, but I don't know what to do with table:
For text object I've used formula:
Num(
Avg(
Aggr(1-Sum(Fact1)/Sum(Fact2),Date)
),
'# ##0,000%')
But it doesn't fit for table (second expression in table).
I tried to add field "Channel" to Aggr expression, but still doesn't work properly (first expression in table).
Here you are
If(Dimensionality() = 0, Avg(Aggr(1-Sum(Fact1)/Sum(Fact2), Date)), Avg(Aggr(1-Sum(Fact1)/Sum(Fact2), Date, Channal)))
try
Num(
Avg( TOTAL
Aggr(1-Sum(Fact1)/Sum(Fact2),Date)
),
'# ##0,000%')
or
Num(
Avg(
Aggr(NODISTINCT (1-Sum(Fact1)/Sum(Fact2)),Date)
),
'# ##0,000%')
Vineeth, thanx for your reply, but it still doesn't fit (same values for C1 and C2).
This one should, I think
Num(
Avg( TOTAL
Aggr(1-Sum(Fact1)/Sum(Fact2),Date)
),
'# ##0,000%')
try below
Num(
Avg( TOTAL
Aggr(1-Sum(Fact1)/Sum(Fact2),Date)
),
'# ##0,000%')
Hi,Sunny! Nope, still doesn't fit:
Channal | Date | Fact1 | Fact2 |
C1 | 01.01.2017 | 10 | 100 |
C1 | 02.01.2017 | 8 | 70 |
C2 | 01.01.2017 | 3 | 10 |
C2 | 02.01.2017 | 5 | 9 |
Nope, doesn't work (see previous answer, please).
Why add <Channel>?
This worked for me
Avg(TOTAL Aggr(1-Sum(Fact1)/Sum(Fact2), Date))
I guess what is the expected output here?
Sunny,
I expect to see that table:
Channal | Value |
Total | 85,863% |
C1 | 88,182% |
C2 | 83,544% |