topic Re: value for max date in each month in QlikView

value for max date in each month

sanjeeva_279 — Sun, 08 Sep 2019 21:21:17 GMT

i needto get value of max date in a month

jan 31

feb 28

mar 15 ,if i have data till 15 only

i tried with date=max(date) and sum of value but no luck,thanks

Re: value for max date in each month

DavidM — Mon, 09 Sep 2019 05:41:41 GMT

hi, since you need to group the data by month since you're looking for max date for each month. The way I would do it is to identify max date in script and create artificial field for set analysis. I suggest doing this in the script in a way:

Left Join (originaltable)

Load

Max(Date) as Date,

Month,

1 as SetAnalysisFlag

resident originaltable

group by Month

This way max for each month will have 1 in SetAnalysisFlag and you can use it like this:

sum{<SetAnalysisFlag={1}>}value)

Re: value for max date in each month

sanjeeva_279 — Mon, 09 Sep 2019 06:02:37 GMT

Thanks for the mail.user want to pass calender as a filter to choose whatever date he wants

if i get all data by month level,if users go by date level it creates data issues

1.generic report should show data by max date of each month value ,

2.calender as input for start date and end date

Re: value for max date in each month

DavidM — Mon, 09 Sep 2019 06:21:22 GMT

I am not sure, if I understand you correctly, but I think what you need is what I've showed you:

The set analysis will show you value for max date for each month.

You can create filter from the Date field and user can select which months he wants to look at

Re: value for max date in each month

Brett_Bleess — Thu, 19 Sep 2019 15:28:14 GMT

Sanjeeva, I am providing some links that may be of some help given the other posts:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/SetAnalysis/set-analysis-expressions.htm

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

Here is the generic/search link for the Design Blog area in case you want to search on your own:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett