topic Re: Need to plot max week/month value in QlikView

Need to plot max week/month value

anjali0108 — Wed, 06 Jun 2018 10:12:17 GMT

Hi All,

I plotted a bar chart with -

Date/Week/Month/Quarter - as my Dimension (cyclic group)

Sum(CNT) - as my Expression

Now, the chart will look somewhat like(sample):

Now, if someone will change the cyclic group to Month/Week/ Quarter, it will automatically sum up all the values for that and will show the sum as a count.

But, the client needs me to show in the following way:

If a week is selected in x-axis - The bars for all the weeks shall show the max(date's) value for that week

If a month is selected in x-axis - The bars for all the months shall show the max(date's) value for that month...

Like for the above chart,

if the cyclic group is changed to month,

then it shall show

x-axis - May

y-axis - 1 (not 765454)

Hope I am able to explain my requirement here. Thanks.

Re: Need to plot max week/month value

tresB — Wed, 06 Jun 2018 10:25:43 GMT

Could you share your sample qvw?

Re: Need to plot max week/month value

sunny_talwar — Wed, 06 Jun 2018 11:36:55 GMT

May be create flags in the script... for example... for Month...

LOAD Date,

If(Floor(MonthEnd(Date)) = Floor(Date), 1, 0) as MonthEndFlag

FROM ...

and then in your expression

Sum({<MonthEndFlag = {"$(=If(GetCurrentField([GroupName]) = 'Month', '1', '*'))"}, WeekEndFlag .....>} Measure)

Re: Need to plot max week/month value

Anonymous — Wed, 06 Jun 2018 12:45:04 GMT

Hello Anjali,

can you share some sample data?

Best Regards,

Ashok

Re: Need to plot max week/month value

anjali0108 — Thu, 07 Jun 2018 09:19:06 GMT

Hi Sunny,

I tried making flags too still no luck.

It is still giving the same count i.e 765455(sum of both days).

I am attaching the qvw for reference. Please let me know whats wrong here.

Thanks for all the help.

Re: Need to plot max week/month value

anjali0108 — Thu, 07 Jun 2018 09:24:20 GMT

PFA

Re: Need to plot max week/month value

sunny_talwar — Thu, 07 Jun 2018 09:53:18 GMT

I guess I didn't really notice that what you wanted was not really MonthEnd... it was just the max value for the month.

Re: Need to plot max week/month value

sunny_talwar — Thu, 07 Jun 2018 09:57:24 GMT

May be try this

If(GetCurrentField([time]) = 'monthname',

FirstSortedValue(Aggr(Sum(CNT), REPORT_DATE), -REPORT_DATE),

Sum(CNT))