Solved

How to use date metric into next filter metric - MAQL


Hello, please help me.

 

Table A

id_table_a  INT

a_date       DATE

 

I would like to find rows for LAST MONTH a_date.

 

 

icon

Best answer by Michael Ullock 2 March 2022, 00:07

View original

12 replies

Userlevel 3

Hello,

What seems to be an issue here, please? I assume that you are working with our Insights and KPI Dashboards, but please correct me if I am wrong.

You can work with FOR PREVIOUS clause directly in your measure or you can use the KPI Dashboard Date filter, concretely the Predefined period for the Last month.

Thank you for help, but I did not right result for my metric.

 

I created metric:

SELECT COUNT(id_table_a) FOR PREVIOUS (Month/Year (a_date)).  

I got all rows without filter .   Where is problem ?

 

 

Userlevel 3

You are very welcome.

The metric looks just fine to me. Please, are you using the Month Date filter on your Dashboard as well?

Also, this might be related to your Logical Data Model itself. Dates are usually uploaded as the standalone datasets, also known as Date Dimensions. You can find more information here.
 

Hello,

Yes, I use date filter on Dashboard without problem.  

Userlevel 3

You may need to utilize a primary key when using the COUNT Function

 

SELECT COUNT(attribute, primary_key)

or

SELECT COUNT(attribute) USING primary_key

Would it be possible to share a screenshot of your LDM?

 

Hello, I send metric and LDM .  This metric still returns all rows.

 

 

Returns 

 

 

 

 

Userlevel 2

Hello, 

from the screenshot of your metric we can see that you're using the date attribute "Month (Date_from_licence)".  Can you please try and update your metric so it uses the date attribute "Month/Year (Date_from_licence)" instead? Once you use this date attribute it will then look for the values for the previous Month/Year. Could you please give this a try and let us know if this helps? 

More details on working with Dates can be found here:
https://help.gooddata.com/pages/viewpage.action?pageId=86795680
 

The same result.

 

 

Userlevel 2

Perhaps I misunderstood the issue here. Could you please try this metric and let us know if this helps? 

SELECT COUNT (id_licence) WHERE Month/Year (Date_from_licence) = THIS -1

The above metric will show you values for only the previous Month only. In the above metric I am incorporating the "THIS"macro, and more details on this can be found here: 
https://help.gooddata.com/pages/viewpage.action?pageId=86795491

Hi, but that's not the right idea because you don't know what the last month's dates are. THIS" macro I can't use, because macro solve current month. 

Please, you can solve problem with “previous”.

Thank you.

 

Userlevel 3

I’m sorry if this has somehow slipped through the cracks, but would it be possible for us to open a ticket with you so you can invite our support user into your workspace? This way we can easily see how to build this to your desired results. 

Reply