3 Replies Latest reply: Oct 12, 2016 9:31 AM by Alejandro Rouiller

# help needed with metric

Hi folks,

First of all, I'm not fluent in english, sorry for that.

Here is the situation.

I'm exploring some solutions to explote de data in a datamart and I have a problem with a metric.

I'm trying to create a single metric to measure the quantity of clients.

The behaviour of the metric should be  the next:

When the level of analysis is "month" the metric should show the number of clients in that mont ( SUM(CLIENT_QUANTITY) )

But, when the level of analysis is "year" the metric show show the number of clients in the

last month's year that have data.

The (partial) structure of the data mart is the next:

Date dimmension

SK_DATE

DATE_STR (the representation of the date in dd/MM/yyyy format)

DAY_OF_MONTH

MONTH_NUMBER (with values from 1 to 12)

YEAR

MONT_YEAR (an integer value compossed by YEAR*100 + MONTH_NUMBER)

Client dimmension

SK_CLIENT

CLIENT_NAME

CLIENT_SUR_NAME

The fact table:

is a factless fact, and its granularity is one record per client at the first day of the mont

SK_CLIENT

SK_DATE

CLIENT_QUANTITY (always 1)

Kind regards,

• ###### Re: help needed with metric

you can try to sum the quantity of the max month with quantity, something like

 sum( {\$ 0"}>}  MONT_YEAR ))"} >} CLIENT_QUANTITY)
• ###### Re: help needed with metric

The metric worked ok, but it only shows the value for the last month in the last year.

I need the same value but for every last month (with data) in every year. Is there a way to achive this?

Thanks again.

• ###### Re: help needed with metric

You can aggregate the metric

Aggr(SUM(CLIENT_QUANTITY) ),Month,Year).

Or Use Drill Down Qilsense

Drill Down Dimensions in Qlik Sense