Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Count from the very beginning to the last month

Hi,

Admittedly I have be struggling with an expression that counts IDs from inception of a business to the last month.

Here is the expression that does the cummulative to date which works just fine

= Count( {<Date={'<=$(vMaxDate)'}>}   DISTINCT(ID))

So assuming the Year =2014 and Month= July I need an expression the "says count all ID from day one to the 30/JUNE/2014".

Not getting the results when I use this expression

= Count( {<Date={'<=$((MonthStart(vMaxDate)-1))'}>}   DISTINCT(ID ))

THANKS!

4 Replies
avinashelite

Hi,

According to your expression its only taking the 1st day for June i think.

Please please try the below format:

vMaxDate store the max month i.e month(Max(Date))

then =Count( {<Month(Date)={'<=$((vMaxDate)-1))'}>}   DISTINCT(ID ))

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try like this

= Count( {<Date={'<=$((Date(MonthStart(vMaxDate)-1)))'}>}   DISTINCT(ID ))


-1 will convert date to number, using Date() will convert number to Date.


Regards,

Jagan.

Anonymous
Not applicable
Author

Hi,

Avinash R  : I get an error in set modifier expression.

Jagan : Your expression returns the same value when counting to current date because its cummulative the last month should always be lesser if not the same.


Thanks

avinashelite

can you post your qvw file. which will help us to check the issue.