Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
M_Zaki
New Contributor III

subtract values from different days in one measure

I need to measure sum of values from different days(rows) and different types.

lets say i have the below data:

Capture.JPG

I need to a measure to calculate difference between sum(values for 'Advance recharge' for specific day) - sum(values of 'external transfer' and 'balance' for 2 days before 'advance recharge' date)

Note: Dimensions would be: Tx_Account & Day

3 Replies
Highlighted
Partner
Partner

Re: subtract values from different days in one measure

Hard to help properly without a bit more info on your logic. but did you try below or above function?
if days is a dimension something like sum(values for 'Advance recharge') - below(sum(values of 'external transfer' and 'balance' )
Highlighted

Re: subtract values from different days in one measure

May be

Sum(Tx_Amount)-Above(Sum(Tx_Amount))

Or

Sum(Tx_Amount)-Sum(Aggr(If(Tx_Type=Above(Tx_Type)Above(Sum(Tx_Amount))),Tx_Account, Tx_ID, Tx_Type, Tx_UPDATE_DATE))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
M_Zaki
New Contributor III

Re: subtract values from different days in one measure

OK, i'll try to describe more:

for below data:

Capture.JPG

I need to create pivot table as:

Capture1.JPG

in "Diff" measure, i need to calculate difference between the "Advance Recharge" in this date (-minus) the values for "Balance" and "External Transfer" but for 2 days before the date dimension field

So "Diff" for 2018/12/11 should calculate: Sum(<Advance Recharge> for 2018/12/11) - Sum(<Start Balance, External Transfer> for 2018/12/10 & 2018/12/9)