Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
not sure if I understood correctly...
You are using a (table) chart, with dimension iMonth, correct?
Maybe you want to try this (I just added iMonth to your aggr-dimensions and one missing bracket):
=aggr(sum(if(iMonth=CurrentMonth,qty_a,0)),Label,iMonth)
/ aggr(sum(if(iMonth=CurrentMonth,qty_b,0)),Label,iMonth)
Regards,
Stefan
Hi Swuehl,
Using a pivot, and the data is loaded from a database.
The goal is to compare current vs last month to get a trend... (in the end).
Your suggestion didnt work, but I get no errors eithers.
LOAD * INLINE
[iMonth, Label, qty_a, qty_b
1, A, 2, 10
1, A, 2, 5
2, A, 1, 20
2, A, 3, 10
];
//Or a variable...
LOAD * INLINE
[CurrentMonth
2
];
vlastMonth=CurrentMonth-1
aggr(sum(if(iMonth=$(CurrentMonth),qty_a,0)),Label,iMonth)
/ aggr(sum(if(iMonth=$(vlastMonth),qty_b,0)),Label,iMonth)
hope this help
Hi tobbeandersson,
I put my exact above expression in a pivot and it seems to work (I attached my sample, please have a look).
(I duplicated your data up to december, because current month is September, so I assume we want to have data for todays month?)
If you want to compare current vs last month in a table with iMonth dimension, you may wanna take a look into the chart inter record function, above() for example.
I created a second table as example for that.
Regards,
Stefan
Hi Stefan swuehl
I am struggling with a similar topic. I would like to use two conditions within the aggr function to calculate how many clients buy from the two products. See below my sample table;
Account | Product1 | Product2 |
Client1 | 1 | 0 |
Client2 | 20 | 0 |
Client3 | 100 | 50 |
Client4 | 0 | 0 |
Client5 | 0 | 30 |
Client6 | 40 | 0 |
Client7 | 0 | 0 |
Client8 | 20 | 0 |
Client9 | 30 | 30 |
Client10 | 0 | 0 |
Client11 | 10 | 0 |
The expression I am using for counting the clients that buy from product1 is the below;
Count(distinct if(aggr(sum({$<[Product Line] ={'Product1'}>} Run_Rate__c), Account) > 0, Account))
How can I add into the aggr function the second product, Product2, so I can get only the clients that buy from both. In this case, 2 will be the answer.
Appreciate very much your help
Maybe like
=Count(DISTINCT
Aggr(
If( Sum({<[Product Line] = {'Product1'}>} Run_Rate_c) >0
and Sum({<[Product Line] = {'Product2'}>} Run_Rate_c) >0, Account)
,Account)
)
Or
=Count(DISTINCT
{<Account = {"=Sum({<[Product Line] = {'Product1'}>} Run_Rate_c) >0
and Sum({<[Product Line] = {'Product2'}>} Run_Rate_c) >0"}
>}
Account)
Maybe it's enough to check if there is a relation between Account and the two Product lines:
=Count(DISTINCT
{<
Account = p({<[Product Line] = {'Product1'}>}) * p( {<[Product Line] = {'Product2'}>})
>}
Account)
Got it, wonderful. Your first solution worked nicely.
Many thanks Stefan