Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there
This is a simple table what my souce table looks like
Below is what i would like to achieve:
If i choose AF1 in the filter, then we hope it can return the number:2
Because once we chosed AF1, then all the values 1 can been seen as 0. In this situation, ID 4 and 7 will have 0 for all hard checks, so 2 cases is the number we need
If we choose UW1 and UW2 in the filter part, then it should return the number:4
Because UW1 and UW2 are chosed in the filter, all the values in thest two column will change to 0. In this situation: ID 1, ID 2, ID 8 and ID 9 will have all the column values with 0
plz help me , thx in advance.
hi
there is quite an easy solution
in your load script cross table your source table for example
[Sheet1$]:
CrossTable (Status,Data,1)
LOAD [ID],
[UW1],
[UW2],
[UW3],
[UW4],
[AF1],
[AF2],
[AF3],
[AF4]
FROM [lib://AttachedFiles/Book1.xls]
(biff, embedded labels, table is Sheet1$);
then in your model
crate a calculated dimension with this expression
aggr(if(sum({<Status=e(Status)>}Data)=0,ID),ID)
this will get you your answer
hi
there is quite an easy solution
in your load script cross table your source table for example
[Sheet1$]:
CrossTable (Status,Data,1)
LOAD [ID],
[UW1],
[UW2],
[UW3],
[UW4],
[AF1],
[AF2],
[AF3],
[AF4]
FROM [lib://AttachedFiles/Book1.xls]
(biff, embedded labels, table is Sheet1$);
then in your model
crate a calculated dimension with this expression
aggr(if(sum({<Status=e(Status)>}Data)=0,ID),ID)
this will get you your answer
Hi Liron
thanks for your reply , I followed your step but i still can't get the right answer
counld you plz attach your test file ?
thx
Hi Dave
Could you perhaps post the sample data?
What is the logic behind your "Hard Checks" , how do you get the result 2 when selecting AF1?
The way I understand it , you want to count all "1" in the selected field when all the other fields =0?
Hi , problem solved
"count(aggr(if(sum({<Status=e(Status)>}Data)=0,ID),ID))" is what i want
thank you for your advice , it's quite helpful
Hi
when i selected AF1 , all the values in AF1 can be seen as 0 , i want to count rows that all columns is 0 , so the answer is 2.