Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
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.