Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

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

Tags (1)
11 Replies
sunny_talwar
Not applicable

Re: Sum based on dates

May be try like this

Dimension

DayName(Date)

Measure

Sum(Sales)

Not applicable

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

passionate
Not applicable

Re: Sum based on dates

Hi Linus,

Creating a master calendar can solve you issue.

Thanks and Regards,

Pankaj

Not applicable

Re: Sum based on dates

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

Not applicable

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.

sunny_talwar
Not applicable

Re: Sum based on dates

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

Not applicable

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

sunny_talwar
Not applicable

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)

Not applicable

Re: Sum based on dates

It worked, thank you