Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 2 date columns
Date1, Date2, Sales
Jan24, Jan24, 100
Jan24, Feb24, 110
Feb24, Feb24, 200
Feb24, Mar24, 210
Mar24, Mar24, 300
Mar24, Apr24, 310
I have to show a bar chart with
DIMENSION = Date1
MEASURE = Sum(Sales) for Date2 as next month
I mean, Jan24 bar should show 110, Feb24 bar should show 210 and Mar24 bar should show 310.
How to write set expression.
Does this work for you :
tmpData:
LOAD * Inline [
Date1, Date2, Sales
01/01/2024, 01/01/2024, 100
01/01/2024, 01/02/2024, 110
01/02/2024, 01/02/2024, 200
01/02/2024, 01/03/2024, 210
01/03/2024, 01/03/2024, 300
01/03/2024, 01/04/2024, 310
];
Data:
NoConcatenate Load *, If(Date2> Date1,1,0) As DateCheck Resident tmpData; Drop Table tmpData;
Then expression is : sum({<DateCheck = {'1'}>}Sales)
Gives:
Does this work for you :
tmpData:
LOAD * Inline [
Date1, Date2, Sales
01/01/2024, 01/01/2024, 100
01/01/2024, 01/02/2024, 110
01/02/2024, 01/02/2024, 200
01/02/2024, 01/03/2024, 210
01/03/2024, 01/03/2024, 300
01/03/2024, 01/04/2024, 310
];
Data:
NoConcatenate Load *, If(Date2> Date1,1,0) As DateCheck Resident tmpData; Drop Table tmpData;
Then expression is : sum({<DateCheck = {'1'}>}Sales)
Gives:
Hi @mohan2391
Dimension: Use Date1.
Measure: Use Sum(SalesForNextMonth).
RawData:
LOAD
Date1,
Date2,
Sales
INLINE [
Date1, Date2, Sales
Jan24, Jan24, 100
Jan24, Feb24, 110
Feb24, Feb24, 200
Feb24, Mar24, 210
Mar24, Mar24, 300
Mar24, Apr24, 310
];
NextMonthSalesMap:
MAPPING LOAD
Date1,
Sales AS NextMonthSales
RESIDENT RawData
WHERE Date1 <> Date2;
FinalData:
LOAD
Date1,
ApplyMap('NextMonthSalesMap', Date2, 0) AS SalesForNextMonth
RESIDENT RawData
WHERE Date1 = Date2;
DROP TABLE RawData;
Hope this resolve your issue.
When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions").
Thanks a lot dmac1971. It worked perfectly.
Thank You TauseefKhan for responding to my question and your suggestion.
The other logic worked fine for me, so didn’t implement in my application.
But your logic seems also working, have tested with some sample data, Thank You.