1

I have been recently working with GOOD-DATA platform. I don't have that much experience in MAQL, but I am working on it. I did some metric and reports in GOOD-DATA platform. Recently I tried to create a metric for calculating Total Buyers,First Time Buyers and Repeating Buyers. I created these three reports and working perfect.But when i try to add a order date parent filter the first time buyers and repeating buyers value getting wrong. please have Look at Following queries.

I can find out the correct values using sql queries.

MAQL Queries:

TOTAL ORDERS- SELECT COUNT(NexternalOrderNo) BY CustomerNo WITHOUT PF TOTAL FIRSTTIMEBUYERS- SELECT COUNT(CustomerNo) WHERE (TOTAL ORDER WO PF=1) WITHOUT PF TOTAL REPEATINGBUYERS- SELECT COUNT(CustomerNo) WHERE (TOTAL ORDER WO PF>1) WITHOUT PF

Can any one suggest a logic for finding these values using MAQL

KrishnKumar.K
  • 31
  • 1

2 Answers2

1

It's not clear what you want to do. If you could provide more details about the report you need to get, it would be great.

It's not necessary to put "without pf" into the metrics. This clause bans filter application, so when you remove it, the parent filter will be used there. And you will probably get what you want. Specifically, modify this:

SELECT COUNT(CustomerNo) WHERE (TOTAL ORDER WO PF>1) WITHOUT PF

to:

SELECT COUNT(CustomerNo) WHERE (TOTAL ORDER WO PF>1)
byrot
  • 11
  • 1
1

The only thing you miss here is "ALL IN ALL OTHER DIMENSIONS" aka "ALL OTHER". This keyword locks and overrides all attributes in all other dimensions—keeping them from having any affect on the metric. You can read about it more in MAQL Reference Guide.

FIRSTTIMEBUYERS:

SELECT COUNT(CustomerNo) 
WHERE (SELECT IFNULL(COUNT(NexternalOrderNo), 0) BY Customer ID, ALL OTHER) = 1

REPEATINGBUYERS:

SELECT COUNT(CustomerNo) 
WHERE (SELECT IFNULL(COUNT(NexternalOrderNo), 0) BY Customer ID, ALL OTHER) > 1
Josef Pithart
  • 276
  • 1
  • 4