show max(date) per month in bar chart

[amruta_j]

Hi all,

I've weekly data in my app. I want to show max of week data for last 6 months, but it should be dynamic.

In attachment, currently I'm showing weekly data per month. So, as per misdate selection last bar will change.

eg: If I select 15 August, then last bar/dot will show 15th Aug week data, but other bars(month-6) will show last weeks data for respective month. so technically there will be one bar/dot per month.

Can you please help me with that.

Thank you in advance!

[jyothish8807]

Can you please share a sample qvw with the exp you are using currently.

Br,

KC

[amruta_j]

I cannot share the app as data is sensitive. But below are the formulae used:

Dimension : =if(MisMonthYear >= vMHalfYear ,MisMonthYear )

where vMHalfYear  =num(addmonths(max(misdate),-6)),

              MisMonthYear=date(misdate,'MMM-YYYY')

Measures: 

=sum({<misdate={'>=$(=addmonths(max(misdate)),-6)<=$(=max(misdate))'},Month=,Year=>}txnamt_sgd)/1000000

[Victor_Alumanah]

You may be able to modify this script to help you

=count({<DATE_COLUMN = {">=$(=MonthStart(addmonths(max(DATE_COLUMN),-6))) <$(=Monthstart(addmonths(max(DATE_COLUMN),1)))"}>}Sales)

Also check this link too MTD

[amruta_j]

Thanks for quick replies. But my formula is working fine.

I just want to restrict weekly data on X axis, like last week of each month by default. Main problem is it should be acting on Filter as well.

Misdate filter is basically date for each Sunday every month. So if I select 08/09/2019, then it should show me data as of 8th Sept for Sept month, but for Aug it should take 25th Aug as it is max(misdate) for August.

I hope I'm clear, if not please let me know.