topic Re: Sum based on dates in App Development

Sum based on dates

Wed, 14 Jun 2017 06:22:05 GMT

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

sunny_talwar — Wed, 14 Jun 2017 07:50:13 GMT

May be try like this

Dimension

DayName(Date)

Measure

Sum(Sales)

Re: Sum based on dates

Wed, 14 Jun 2017 08:06:08 GMT

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

passionate — Wed, 14 Jun 2017 08:08:44 GMT

Hi Linus,

Creating a master calendar can solve you issue.

Thanks and Regards,

Pankaj

Re: Sum based on dates

Wed, 14 Jun 2017 09:15:17 GMT

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

Re: Sum based on dates

Wed, 14 Jun 2017 10:23:20 GMT

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.

Re: Sum based on dates

sunny_talwar — Wed, 14 Jun 2017 15:20:01 GMT

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

Re: Sum based on dates

Thu, 15 Jun 2017 10:17:45 GMT

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

sunny_talwar — Thu, 15 Jun 2017 10:57:25 GMT

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

Thu, 15 Jun 2017 11:25:32 GMT

It worked, thank you

Re: Sum based on dates

Fri, 16 Jun 2017 07:00:53 GMT

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

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

Re: Sum based on dates

sunny_talwar — Fri, 16 Jun 2017 08:41:29 GMT

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.