# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save \$300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
Highlighted
New Contributor III

## Daily data compare in end of month

Hello All,

I have daily comparing data in any object, but I have some problem in my analysis in the end of month.

basically my analysis is daily analysis (compare date to date), but in the end of month I wanna compare

total this month vs total last month. I use this formula for daily analysis :

=sum({<Date_Fix={">=\$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=\$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty).

The problem when use that formula, when this month (February) max date is 28, but last month (January) max date is 31.

analysis will be 28 Feb vs 28 Jan. can I change the formula when reaching end of month become 28 Feb vs 31 Jan.

Does anybody know the tricky?

Thank you

Tags (1)
1 Solution

Accepted Solutions
Valued Contributor III

## Re: Daily data compare in end of month

If (Max (Date_Fix)=MonthEnd (Max (Date_Fix)),

sum({<Date_Fix={">=\$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=\$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

/

,

sum({<Date_Fix={">=\$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=\$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

/

)

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
6 Replies
Valued Contributor III

## Re: Daily data compare in end of month

Cheers

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
New Contributor III

## Re: Daily data compare in end of month

Hello Thiago,

Your formula is worked to find value in last month right?

my issue is,

how we find total end of month VS end of month for comparing achievement in the end of month.

Example :

=sum({<Date_Fix={">=\$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=\$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

/

above formula result is : total 1-28 Feb 18 / total 1-28 Jan 18.

All I want is to find result :

total 1-28 Feb 18 / total 1-31 Jan 18.

But also can see result by daily, for example max date is 20, and the formula become :

total 1-20 Feb 18 / total 1-20 Jan 18

Thank you

Valued Contributor III

## Re: Daily data compare in end of month

What if we use this:

=sum({<Date_Fix={">=\$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=\$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

/

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
Valued Contributor III

## Re: Daily data compare in end of month

If (Max (Date_Fix)=MonthEnd (Max (Date_Fix)),

sum({<Date_Fix={">=\$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=\$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

/

,

sum({<Date_Fix={">=\$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=\$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

/

)

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
New Contributor III

## Re: Daily data compare in end of month

Hello Thiago,

with a little modification (underline), I get the logic. and here is my formula :

If (Day(Max({<DESC= {'SALES'}>}Date_Fix))=Day(MonthEnd(Max({<DESC= {'SALES'}>}Date_Fix))),

(sum({<Date_Fix={">=\$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=\$(=max({<DESC= {'STU'}>}Date_Fix))"},DESC= {'STU'},YEAR,MONTH,DAY>}sales_qty)

/

,

(sum({<Date_Fix={">=\$(=MonthStart(max({<DESC= {'SALES'}>}Date_Fix)))<=\$(=max({<DESC= {'SALES'}>}Date_Fix))"},DESC= {'SALES'},YEAR,MONTH,DAY>}sales_qty)

/

)

Regard.

Valued Contributor III

## Re: Daily data compare in end of month

Sound's great man.

Cheers

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago