Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a pivot table that display trands,
with two calculated dimensions "Overall Trend" and "Last Two Qtr Trend"
I am using bundle info to show pics insted of values,
now the client wants this pics as filters that hew can use on the side,
"Overall Trend" calculated dimension in the table:
=aggr(if(sum(AttentionFlag)>=2,Only({$<Alabet={'a'}>}info(Alabet)),Only({$<Alabet={'b'}>}info(Alabet))),[Father Supplier Name])
"Overall Trend" calculated dimension in the listbox:
=aggr(if(sum(AttentionFlag)>=2,Only({$<[Father Supplier Name]=,Alabet={'a'}>}info(Alabet)),Only({$<[Father Supplier Name]=,Alabet={'b'}>}info(Alabet))),[Father Supplier Name])
the calculation in the listbox keep excluding the other picture,
See th Attached.
help is appreciated.
thanks.
I am basically using an alternate state AS1 for the second list box and then use
=aggr(only({<AttentionSign-={'a'},[Father Supplier Name]=,[Year-Qtr-Num]={'$(=max({$}[Year-Qtr-Num]))'}>}info(AttentionSign)),[Father Supplier Name])
and as expression in the chart
=AVG({$*AS1}[Final Score])
there currently might be some minor open issue with regard to e.g. selections in time (not sure how the requirements are here, the current list box 1 also seems to not show the correct filter options when e.g. selecting a quarter).
Try this:
=Aggr(If(Sum({<[Father Supplier Name] = >} AttentionFlag) >= 2, Only({$<[Father Supplier Name] = ,Alabet = {'a'}>} info(Alabet)), Only({$<[Father Supplier Name] =, Alabet = {'b'}>} info(Alabet))), [Father Supplier Name])
Or may be this:
=Aggr(If(Sum({<[Father Supplier Name] = , Alabet =>} AttentionFlag) >= 2, Only({$<[Father Supplier Name] = ,Alabet = {'a'}>} info(Alabet)), Only({$<[Father Supplier Name] =, Alabet = {'b'}>} info(Alabet))), [Father Supplier Name])
thank you sunny it seems ok but i have another filer,
"Last Two Qtr Trend" that i am allso ignoring [Father Supplier Name],
"Last Two Qtr Trend" in this listbox:
=aggr(only({$<AttentionSign-={'a'},[Father Supplier Name]=,[Year-Qtr-Num]={'$(=max([Year-Qtr-Num]))'}>}info(AttentionSign)),[Father Supplier Name])
and its ok,
but when i am filtering one of the filters the table is filtering ok but in the same scenario i am filtering
the other filter and it is canceling the previus filter,
i believe it is because we are ignoring [Father Supplier Name] the second filter is catching all the [Father Supplier Name]
in the model without paying attention to the preveius filter.
is ther somthing i can do?
hope it's understood.
thanks.
Never tried this before, can you give this a try:
=Aggr(Alt(Only({$<[Father Supplier Name] = {"=Sum({<[Father Supplier Name] = >} AttentionFlag) >= 2"}, Alabet = {'a'}>} info(Alabet)), Only({$<[Father Supplier Name] = {"=Sum({<[Father Supplier Name] = >} AttentionFlag) < 2"}, Alabet = {'b'}>} info(Alabet))), [Father Supplier Name])
no sunny its not good at all now,
it is only showing one value from the two.
see the attached
Would you be able to share a sample?
I am not sure, i need to check - i dont think the client will approve.
is it simple to scrable the data can you guide me how?
Look here to scramble the data
Preparing examples for Upload - Reduction and Data Scrambling
thank you sunny,
we will continue this later after i will scramble the data.
many thanks.