Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am new to Qlik Sense. I have the data like below.
ID | Month | Value |
A | Dec | 10 |
B | Dec | 20 |
C | Dec | 30 |
A | Jul | 100 |
B | Jul | 200 |
C | Jul | 300 |
D | Jul | 400 |
Now I need to show Jul values in separate column if Jul ID is matching with Dec ID.
Expected output shown below:
ID | Month | Value | July month values for same id |
A | Dec | 10 | 100 |
B | Dec | 20 | 200 |
C | Dec | 30 | 300 |
I cannot do any changes in Script editor. I have to do it on sheet (Front end).
I used this expression for 4th column.
As below
temp:
load
ID
,Monthstart(date#(Month,'MMM-YY')) as Date
,MonthName(date#(Month,'MMM-YY')) as Month
,Value
inline [
ID,Month,Value
A,Dec-22,10
B,Dec-22,20
C,Dec-22,30
A,Jul-22,100
B,Jul-22,200
C,Jul-22,300
D,Jul-22,400
A,Sep-22,1000
B,Sep-22,2000
C,Sep-22,3000
D,Sep-22,4000
];
exit Script;
Chart
Dimensions
=ID
=Aggr({<Month=>}Only({<Date={"$(=Max({<Month=>}Date))"}>}Month),ID)
Measure
Current Month = Sum({<Date={"$(=Max({<Month=>}Date))"},Month=>}Value)
Selected Month = Sum({<Date={"$(=Max(Date))"}>}Value)
If you dont intend to use the month field in the table, then it could be done easily, just use the if and else condition to create a set of expressions for each month.
For example;
Aggr(if (match(ID, ID), Value), Month)
@deepanshuSh , Thank you for your reply.
If there only two values in month field, it may work. But I have multiple months and I have stored a selected month as my variable input.
Suppose if I select Sept month, then 4th column should be Selected month values.
As below
temp:
load
ID
,Monthstart(date#(Month,'MMM-YY')) as Date
,MonthName(date#(Month,'MMM-YY')) as Month
,Value
inline [
ID,Month,Value
A,Dec-22,10
B,Dec-22,20
C,Dec-22,30
A,Jul-22,100
B,Jul-22,200
C,Jul-22,300
D,Jul-22,400
A,Sep-22,1000
B,Sep-22,2000
C,Sep-22,3000
D,Sep-22,4000
];
exit Script;
Chart
Dimensions
=ID
=Aggr({<Month=>}Only({<Date={"$(=Max({<Month=>}Date))"}>}Month),ID)
Measure
Current Month = Sum({<Date={"$(=Max({<Month=>}Date))"},Month=>}Value)
Selected Month = Sum({<Date={"$(=Max(Date))"}>}Value)
@vinieme12 , Thank you so much