Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
arethaking
Creator II
Creator II

Set Expression for Sales

Hi All,

I want to find the Sales2 for my below dataset.

 

For selections: 

End_Month Apr-20,Emp2 there are no APR-20 Sales1 values but Sales1 data is there for JAN-20,FEB-20,MAR-20.

End_Month Apr-20,Emp5 there are no APR-20 Sales1 values but Sales1 data is there for MAR-20

My requirement is to be shown as mentioned below:

For Emp2,End_Month values JAN-20,FEB-20,MAR-20,Sales2

For Emp5,End_Month values , MAR-20,Sales2

End_Month

I wrote this expression seems not working.

=Aggr(Only({<Start_Month=p(End_Month)>}End_Month), End_Month, EmpName)

@Kushal_Chawda 

@sunny_talwar 

@jagan 

 

Capture.JPG

2.JPG

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

Hi,

Please update your script as below

LOAD *,
date(MonthEnd(End_Date),'MMM-YY') & '_' & EmpName as End_Key,
date(MonthEnd(Start_Date),'MMM-YY') & '_' & EmpName as Start_Key,
date(MonthEnd(End_Date),'MMM-YY') as End_Month,
date(MonthEnd(Start_Date),'MMM-YY') as Start_Month,
Year(Start_Date) as Start_Year,Year(End_Date) as End_Year;
LOAD End_Date,
Start_Date,
EmpName,
Sales1,
Sales2
FROM
Data - Copy.xlsx
(ooxml, embedded labels, table is Data);

 

And create chart as below

Dimensions: EmpName, End_Month

Expression: sum({<End_Key=P({<End_Key=>}Start_Key)>}Sales2)

 

Regards,

Jagan.

 

 

 

 

View solution in original post

3 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Please update your script as below

LOAD *,
date(MonthEnd(End_Date),'MMM-YY') & '_' & EmpName as End_Key,
date(MonthEnd(Start_Date),'MMM-YY') & '_' & EmpName as Start_Key,
date(MonthEnd(End_Date),'MMM-YY') as End_Month,
date(MonthEnd(Start_Date),'MMM-YY') as Start_Month,
Year(Start_Date) as Start_Year,Year(End_Date) as End_Year;
LOAD End_Date,
Start_Date,
EmpName,
Sales1,
Sales2
FROM
Data - Copy.xlsx
(ooxml, embedded labels, table is Data);

 

And create chart as below

Dimensions: EmpName, End_Month

Expression: sum({<End_Key=P({<End_Key=>}Start_Key)>}Sales2)

 

Regards,

Jagan.

 

 

 

 

View solution in original post

jagan
Luminary Alumni
Luminary Alumni

Try this it is working for End Month Selection

sum({<End_Month=, End_Key=P({<End_Month=, End_Key=>}Start_Key)>}Sales2)

jagan
Luminary Alumni
Luminary Alumni

Please find attached file for solution.