Monthly bar chart - help

[mohan2391]

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.

[dmac1971 · Accepted solution]

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:

[TauseefKhan]

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").

[mohan2391]

Thanks a lot dmac1971. It worked perfectly.

[mohan2391]

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.