Qlik Community

New to Qlik Sense

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

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik1_User1
Specialist
Specialist

Urgent help needed please with Set Analysis expression creation -- Sum of sales by grouping of dates

Urgent help needed please with set expression

Dim: Date in MMM-YY format

Measure: Sum(salary),Count(ID)

But the trick over here is if date column has same month , same year and has same Status. Than show sum of sales for both date under that Month year.

DateSalaryIDStatus
3/1/2020101A
3/15/2020121A
4/1/2020232B
4/12/2020451B
6/29/2020891A
7/1/2020651A
8/1/2020781B

 

Expected output

DateSalaryIDStatus
Mar-20222A
Apr-20683B
Jun-20891A
Jul-20651A
Aug-20781B

 

Please help!!

1 Solution

Accepted Solutions
martinpohl
Partner
Partner

Hi,

the problem is 

Date(rdate,'MMM-YY')as newdate

with that you format the date to new field, but insight, the date still knows the separate date information, so there are still copies from the same MMM-DD field

change to

Date(monthstart(rdate),'MMM-YY')as newdate

so insight, the newdate field references to monthstart from the dedicated date

Regards

View solution in original post

9 Replies
TimvB
Creator II
Creator II

Add a table object to the sheet and add the following columns:

Dimensions: Date(DateField,'MMM-YY'), Status

Measures: Sum(salary), Count(ID)

This will give you the expected result.

Channa
Specialist III
Specialist III

Main:
LOAD
"Date",
Text( Date("Date",'MMM-YY')) as DateY,
Salary,
ID,
Status
FROM [lib://TEST/Dates.xlsx]
(ooxml, embedded labels, table is Sheet1);

LOAD
DateY,Status,
sum(Salary) as Salary,
Count(ID) as ID

resident Main group by DateY,Status;

Channa
Qlik1_User1
Specialist
Specialist
Author

Yes i have tried this but its not giving the expected output. Please help.

Qlik1_User1
Specialist
Specialist
Author

This need to be done via set expression..as group of data at script level will impact the other charts..please help..

srdheekonda
Contributor III
Contributor III

If you are able to bring the Month Year Column as part of the script then you will get the requried output once you drag the required fields. As Sales is a measure it will group by the unique dimension values. Try to bring the Month column in the script instead of creating Dimension. so you no need to create any set analysis expression.

Qlik1_User1
Specialist
Specialist
Author

I tried this but not working...

Created one new column like
Load
ID,Sales,rdate,status
Date(rdate,'MMM-YY')as newdate
From table a;

srdheekonda
Contributor III
Contributor III

Ideally it should work other wise create one measure with below forumula and use this measure along with other 3 columns in the drilldown:

=SUM(TOTAL <newdate,ID,status> Sales)

srdheekonda
Contributor III
Contributor III

Could you please also check the SUM aggregation applied on Sales column or not. If not applied try to apply and check.

Still if you have issue then go for the measure with mentioned expression.

martinpohl
Partner
Partner

Hi,

the problem is 

Date(rdate,'MMM-YY')as newdate

with that you format the date to new field, but insight, the date still knows the separate date information, so there are still copies from the same MMM-DD field

change to

Date(monthstart(rdate),'MMM-YY')as newdate

so insight, the newdate field references to monthstart from the dedicated date

Regards