Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
marcoyukon
Not applicable

Prior Month Total Set Expression Issue

I have the following set expression. If I use the {1} as the set identifier I get the previous mt total, however the issue I am having is that ANY other dim selections will be disregarded, which is not desirable.

PriorMonth =COUNT( DISTINCT

                    {1<

                        [DateType]={'Refs'},

                        [CanonicalDate.Calendar.YearMonth]={'$(vPreviousCalendarMonth)'}

                    >} RefId )

I've tried to change the set identifier to {$} and also changed the date dimension attribute to ignore, but didn't work. I get zeros for the prior month.

=COUNT( DISTINCT

                    {$<

                        [DateType]={'Refs'},

                        [CanonicalDate.Calendar.YearMonth]=,

                        [CanonicalDate.Calendar.YearMonth]={'$(vPreviousCalendarMonth)'}

                    >} RefId )

Is there a way to ignore the YearMonth and still use any other selections ?

3 Replies
marcelviegas
Not applicable

Re: Prior Month Total Set Expression Issue

Manda o qvw ai se poder.

dwforest
Not applicable

Re: Prior Month Total Set Expression Issue

Separate the set expression

PriorMonth =COUNT( DISTINCT

                    {1<[CanonicalDate.Calendar.YearMonth]={'$(vPreviousCalendarMonth)'}> *

                       $<[DateType]={'Refs'}>} RefId )

It's been a while, you may also have to include every field that you want selection to impact, in the second part,e,g, Department=

sunny_talwar
Not applicable

Re: Prior Month Total Set Expression Issue

How about this

=Count(DISTINCT {1<[DateType]={'Refs'}, [CanonicalDate.Calendar.YearMonth]={'$(vPreviousCalendarMonth)'}>} RefId) * Avg(1)