topic Re: Counting current month totals in Set Analysis Expression in App Development

Counting current month totals in Set Analysis Expression

ZoeM — Fri, 02 Apr 2021 07:42:47 GMT

Hi Community.

I have planned sales data in a monthly line chart from Jan to Dec.

I have the following expression and what I am trying to accomplish is to have a data point that shows the monthly total and changes as we go from month to month:

Count({$<[Date Type]={'VPP Dates'},Reference_Only={'No'},Date={$(=Date(Today(),'MMMM'))}>}Gateways)

I am getting zero when I throw the equation in a text box.

any help rendered will be truly appreciated.

Re: Counting current month totals in Set Analysis Expression

dplr-rn — Thu, 29 Nov 2018 22:33:24 GMT

Date={$(=Date(Today(),'MMMM'))} will not work.
You either have to create a master calendar and do Set analysis against month field in the master calendar.
Or you will need to do something like below
Date={">=$(=MonthStart(Today()))"} , Date={"<=$(=Today())"}

Typing on my phone so may not perfect but you get the idea

Re: Counting current month totals in Set Analysis Expression

ZoeM — Fri, 30 Nov 2018 13:09:59 GMT

Thanks for the response.

I do have a master calendar and when I used your suggested formula, it said the expression was OK but I got a result of zero.

Re: Counting current month totals in Set Analysis Expression

dplr-rn — Fri, 30 Nov 2018 13:24:55 GMT

Previous one you have to consider date format too I believe.
But
If you have a master calendar use set analysis on that along with month function
E.g. month={"$(=month(today ())"}

Share your expression of it doesn't work

Re: Counting current month totals in Set Analysis Expression

ZoeM — Fri, 30 Nov 2018 13:35:43 GMT

This is my Master Calendar data:

LOAD %Date,
Date,
Year,
[Quarter Number],
[Quarter Name],
[Month of Year],
[Month of Year Name],
[Month of Year Abbreviated Name],
[Week of Year],
[Day of Week],
[Day of Week Name],
[Day of Week Abbreviated Name],
[Day of Month],
[Day of Year],
[Days in Month],
Date(MonthStart(Date),'MMM YY') as YearMonth,
[Leap Year Flag],
[Weekend Flag],
[Production Month],
[Production Year],
[Issue Number],
[Julian Date],
[Week Ending Friday]

I tried using Month of Year:

=Date(Today(),'MMM')& ', '& Count({$<[Date Type]={'VPP Dates'},Reference_Only={'No'},[Month of Year]={"$(=[Month of Year](today ())"}>}Gateways)

It still didnt work.

Re: Counting current month totals in Set Analysis Expression

dplr-rn — Fri, 30 Nov 2018 13:44:52 GMT

Use same function on today as when you are creating yearmonth

Count({$<[Date Type]={'VPP Dates'},Reference_Only={'No'},[Month of Year]={"$(=Date(MonthStart(today()),'MMM YY')"}>}Gateways)

Re: Counting current month totals in Set Analysis Expression

ZoeM — Fri, 30 Nov 2018 13:52:33 GMT

Yes I tried that too and it still didn't work...

All I want to show is this months distribution and not every other month (YTD), currently the graph is showing Jan through Dec as the expression is a count of total planned for each month.

Re: Counting current month totals in Set Analysis Expression

dplr-rn — Fri, 30 Nov 2018 13:55:06 GMT

Sorry not Month of Year
YearMonth={"$(=Date(MonthStart(today()),'MMM YY')"}

Re: Counting current month totals in Set Analysis Expression

ZoeM — Fri, 30 Nov 2018 14:08:53 GMT

Nothing Dilipranjith, still nothing...

Re: Counting current month totals in Set Analysis Expression

dplr-rn — Fri, 30 Nov 2018 14:14:19 GMT

weird. can you share the app? (anonymize if sensitive information exists)
in Qlikview - Settings - Document Properties - Scrambling
or
https://community.qlik.com/t5/Qlik-Design-Blog/Scrambling-Data/ba-p/1473042

Re: Counting current month totals in Set Analysis Expression

dplr-rn — Fri, 30 Nov 2018 14:22:10 GMT

Sample app would be ideal Or can you share pictures of what you are getting and dimensions and measures used.
Only 1 measure on your line chart right?
Also try copy pasting the count expression onto a text field. and check if value makes sense

Re: Counting current month totals in Set Analysis Expression

ZoeM — Fri, 30 Nov 2018 14:26:06 GMT

Re: Counting current month totals in Set Analysis Expression

ZoeM — Fri, 30 Nov 2018 14:28:13 GMT

Unfortunately I cannot share the app, even after scrambling, due to confidential restrictions.

Re: Counting current month totals in Set Analysis Expression

dplr-rn — Fri, 30 Nov 2018 14:30:35 GMT

If You have other expressions i believe it will interfere with the set analysis

Re: Counting current month totals in Set Analysis Expression

ZoeM — Fri, 30 Nov 2018 14:40:07 GMT

I disabled the other expressions and still didn't work. So not too sure if that would help.

Re: Counting current month totals in Set Analysis Expression

ZoeM — Fri, 30 Nov 2018 17:14:44 GMT

I think I got it...

I will have to check tomorrow to see if the month will change to December, but thus far I am able to show as intended:

Dual('Current Month, '& Count({$<[Date Type]={'VPP Dates'},Reference_Only={'No'},[Month of Year Abbreviated Name]={$(=Date(Today(),'MMM'))}>}Gateways),
Count({$<[Date Type]={'VPP Dates'},Reference_Only={'No'},[Month of Year Abbreviated Name]={$(=Date(Today(),'MMM'))}>}Gateways) )