Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I have a two straight table with alternate states for comparison of IDs based on date selected.
First table has 15 dimensions.
Second table has 15 Dimension and 1 Measure(which is calculated).Measure is
Count({<where date={first table selected date}>}IDs).I am getting 1's and 0's.So I replaced 1's with 'Exist' and 0's with 'Not Exist' text.It perfectly working but the requirement is the table should be filterable for Exist and not Exists value.
How can i provide a filterpane to user so that he should be able to filter 'Exists' and 'Not Exist' record.
Truly Appreciate your help
Hello Imrasyed,
You can use aggr() function and turn measure into a dimension. You may have to specify all your 15 dimensions of second table as aggr(measure,dim1,dim2....dim15).
Regards,
if((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1)))=0
and only({$*<CreatedDateStart={"$(vMaxCreatedstart)"}>}[Scan Started At]) < '$(vMaxCreatedstart)','Scan Not Performed',
if((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1)))=0,'Fixed',
if((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1)))>0
and only({$*<CreatedDateEnd={"$(vMaxCreatedstart)"},key= {$(vG1Pids)}>}[Scan Started At]) < '$(vMaxCreatedstart)','Scan Not Performed',
if((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1)))>0,'Not Fixed'
))))
Here is my Measure Expression.
I am adding 0*sum().... get all the values of the selected date from first table to that of date in second table.
Please suggest me where should i be adding aggr funcion.
I did this
=if(aggr((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1))),key,CreatedDateEnd)=0
and aggr(only({$*<CreatedDateStart={"$(vMaxCreatedstart)"}>}[Scan Started At]),key,CreatedDateStart) < '$(vMaxCreatedstart)','Scan Not Performed',
if(aggr((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1))),key,CreatedDateEnd)=0,'Fixed',
if(aggr((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1))),key,CreatedDateEnd)>0
and aggr(only({$*<CreatedDateEnd={"$(vMaxCreatedstart)"},key= {$(vG1Pids)}>}[Scan Started At]),key,CreatedDateStart) < '$(vMaxCreatedstart)','Scan Not Performed',
if(aggr((count({$*<key= {$(vG1Pids)}>}distinct key)+0*(sum({<key= {$(vG1Pids)},CreatedDateEnd= {"$(vMaxCreatedstart)"}>}1))),key,CreatedDateEnd)>0,'Not Fixed'
))))
but i am getting only two values 'Not Fixed' and 'Scan Not Performed' the other value i.e 'Fixed' isn't coming.
Can you please have a look into Expression?