Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hamdaniahmad
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

1 Solution

Accepted Solutions
thiago_justen
Valued Contributor III

Re: Daily data compare in end of month

Based on your second condition I guess this will be helpful:

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(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=MonthEnd (AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1)))"},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)

/

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

)

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

Re: Daily data compare in end of month

What about this:

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


Cheers

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
hamdaniahmad
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)

/

sum({<Date_Fix={">=$(=MonthStart(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1))"},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

thiago_justen
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)

/

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

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

Re: Daily data compare in end of month

Based on your second condition I guess this will be helpful:

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(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=MonthEnd (AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1)))"},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)

/

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

)

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

Re: Daily data compare in end of month

Hello Thiago,

Thank you for your sugggestion.

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(AddMonths(max({<DESC= {'SALES'}>}Date_Fix),-1)))<=$(=MonthEnd (AddMonths (max({<DESC= {'SALES'}>}Date_Fix),-1)))"},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)

/

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

)


Regard.

thiago_justen
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