Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
blurrblurr
Contributor III
Contributor III

Grouping data from day to month

Hi,

how can I regroup and count the number of times of item 1 by per month?

grouping.png

expected result

03.2018  | 6

04.2018  | 23

05.2018  | 4

There is more than 1 items to group but I think if I am able to group this. the rest shall be group automatically.

1 Solution

Accepted Solutions
justinphamvn
Creator II
Creator II

Hi,

Try this:

Aggr(count(Distinct [TRANSACTION_DATE.autoCalendar.Date]), TRANSACTION_DATE.autoCalendar.YearMonth, Item)



Hope this helps,

Justin.

View solution in original post

6 Replies
Lisa_P
Employee
Employee

It will depend on your data what is the best way.

If you load using data manager, it will create a YearMonth field which you can use as a dimension or create your own calendar.

You should also have a reference per order of each that you could count.

so table would have

Item     YearMonth     Count(OrderRef)

blurrblurr
Contributor III
Contributor III
Author

I am not using Data manger.

After some attempt using the Expression

Aggr(count(Distinct [TRANSACTION_DATE.autoCalendar.Date]), TRANSACTION_DATE.autoCalendar.YearMonth)

I am now getting biggest record instead of the total sold for the month.

What is wrong with my express?

grouping.png

dilipranjith
Partner
Partner

i am little unclear on the problem. i am assuming you want the total sold for the month

2 questions

- why are you counting the transaction dates? instead of transactionid or something similar

- you seem to have a master/autocalendar - instead of using aggr - just use YearMonth as the dimension simple count function as the measure

blurrblurr
Contributor III
Contributor III
Author

The criteria is a bit unique here. I need to count the sold item as 1 for the day instead of total sold per day.

and with that 1 count per day, I need to summarize the total of date counted by months.

For ex. item 1 sold 18 on 2-Jan-2018. the count will become 1 for 2-Jan-2018 and so on.

I have already use the YearMonth dimension but see the monthly chart is not the sum for all items. 

justinphamvn
Creator II
Creator II

Hi,

Try this:

Aggr(count(Distinct [TRANSACTION_DATE.autoCalendar.Date]), TRANSACTION_DATE.autoCalendar.YearMonth, Item)



Hope this helps,

Justin.

View solution in original post

blurrblurr
Contributor III
Contributor III
Author

Hi Justin.

This is working. thanks!