11 Replies Latest reply: Jun 16, 2017 4:41 AM by Sunny Talwar

# Sum based on dates

Hello,

I have a file containing, among other things, a date field (2017-06-14) and another field containing sales number.

I would like to summarize sales per month as in the example below, summing May and June separately.

I have tried to create dimensions with wildmatch but I can not use that dimension as a field.

Do you have any suggestions?

This is true of Qlik sense desktop

Date                     Sales

2017-05-01            1526

2017-05-02            1236

2017-05-25            548

2017-06-02            485

2017-06-05            2699

• ###### Re: Sum based on dates

May be try like this

Dimension

DayName(Date)

Measure

Sum(Sales)

• ###### Re: Sum based on dates

I´m not sure i understand what you mean, kinda new to Qlik.

If I want to use it as an expression to show number in a meter, lets say I want to sum alla sales for 2017-05.

Even when I try to sum a specifik date i get Zero i result:

Sum(if(Date='2017-05-02',Sales)))

• ###### Re: Sum based on dates

Where are you trying to do this? In a chart with Date as dimension?

• ###### Re: Sum based on dates

I will try put it like this.

All I want to do is so sum the sales for a month based on the dates.

How can I sum sales for May based on this information?

Date                     Sales

2017-05-01            1526

2017-05-02            1236

2017-05-25            548

2017-06-02            485

2017-06-05            2699

• ###### Re: Sum based on dates

May be like this

Sum({<Date = {"\$(='>=' & Date(MakeDate(2017, 5, 1), 'YYYY-MM-DD') & '<=' & Date(MakeDate(2017, 5, 31), 'YYYY-MM-DD'))"}>}Sales)

• ###### Re: Sum based on dates

It worked, thank you

• ###### Re: Sum based on dates

Hello, I found Another way to sum by date that works.

sum(if(wildmatch(Date,'2017-05*'),Sales))

• ###### Re: Sum based on dates

That is great, but I encourage you to use set analysis rather then if statement because set analysis is more efficient in carrying out these operations compared to if statements.

• ###### Re: Sum based on dates

Hi Linus,

Creating a master calendar can solve you issue.

Thanks and Regards,

Pankaj

• ###### Re: Sum based on dates

Can I use master calendar when the date field is created with crosstable?

• ###### Re: Sum based on dates

I have created a dimension called Month like this,  if(wildmatch(Date,'2017-05*'),'Maj17','Other')

This works fine but can i use the dimension in a if statement like    sum(if(Month='Maj17',Sales))

Befor the last uppdate this worked fine but no i get wrong fieldname in Month.