Using MonthNum - 1 in set analysis

helen_pip — Fri, 14 Oct 2016 16:35:14 GMT

Dear Qlikview user

I am trying to create a expression which always counts data from the previous month when a user selects from my month list box:

I.e. If a user selects Aug then the expression should show data for Jul

Month List Box:

Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar

My expression so far is:

count({$<Description = {'01) ABC'},

FactDate = {'>=$(=Monthstart(Addmonths(date(max(FactDate)), -1))) <=$(=MonthEnd(Addmonths(date(max(FactDate)), -1)))'} ,

[Breach Indicator] = {'N'}>}ID_NO)

yet although Monthstart(Addmonths(date(max(FactDate)), -1 and MonthEnd(Addmonths(date(max(FactDate)), -1 do pick up the correct dates, my data is not showing accuratley

Does anyone know a good way to show a count for the previous month when a month name is selected

In addition I have a field called MonthNum which I could use along lines of MonthNum-1

Any help would be greatly appreciated

Thanks

Helen

Re: Using MonthNum - 1 in set analysis

sunny_talwar — Fri, 14 Oct 2016 16:37:58 GMT

May try to ignore Month in your expression:

Count({$<Description = {'01) ABC'}, FactDate = {'>=$(=Monthstart(Addmonths(date(max(FactDate)), -1))) <=$(=MonthEnd(Addmonths(date(max(FactDate)), -1)))'}, Month, [Breach Indicator] = {'N'}>}ID_NO)

topic Using MonthNum - 1 in set analysis in QlikView

Using MonthNum - 1 in set analysis

Re: Using MonthNum - 1 in set analysis