0

I have this table :

Date      Person       Usable
20140101  AAA          TRUE
20140101  AAA          TRUE
20140101  AAA          FALSE
20140101  BBB          FALSE

I want to display the count of usable and the count of not usable filter by date and person.

Date      Person       Usable    Not usable
20140101  AAA          2         1
          BBB          0         1

I don't know how create my metrics for the usable and not usable columns

Fabrice Mainguené
  • 486
  • 1
  • 5
  • 18

1 Answers1

0

You need two conditional metrics.

Create two filters, F_Usable and F_NotUsable:

  • F_Usable will have the condition "Usable = True"
  • F_NotUsable "Usable = False"

Then create the metric Usable as count(Person@ID) [I'm assuming you have an attribute called Person] with the condition F_Usable, then create a metric Not Usable, same formula, but F_NotUsable filter.

Now you can put your metrics in your report.

Another way, more advanced I think, would be to define two facts:

  • Usable as if([Usable ID] = true,1,0)
  • Not Usable as if([Usable ID] = false,1,0)

Then you have to create two metrics which will use these facts.

This second approach will also produce a more performing query, but you are introducing logic in a fact definition. This is possible, but I think this one of those thing to do when you are already very familiar with the MicroStrategy SQL Engine.

mucio
  • 7,014
  • 1
  • 21
  • 33
  • I've tried this solutions but if I have a count of usable = 0, the row doesn't appear – Fabrice Mainguené Jun 30 '14 at 11:05
  • sorry, I missed one step, you should put the metrics in outer join, because, as you noticed, it's possible that one of them returns no values. This should happen only if you use the first approach. If you created two facts with the condition inside than you don't need the conditional metrics and the outer join – mucio Jun 30 '14 at 11:09