New to Qlik Sense

[Qlik1_User1]

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.

Date	Salary	ID	Status
3/1/2020	10	1	A
3/15/2020	12	1	A
4/1/2020	23	2	B
4/12/2020	45	1	B
6/29/2020	89	1	A
7/1/2020	65	1	A
8/1/2020	78	1	B

Expected output

Date	Salary	ID	Status
Mar-20	22	2	A
Apr-20	68	3	B
Jun-20	89	1	A
Jul-20	65	1	A
Aug-20	78	1	B

Please help!!

[TimvB]

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]

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;

[Qlik1_User1]

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

[Qlik1_User1]

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

[srdheekonda]

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]

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]

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]

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 · Accepted solution]

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