Discussion board where members can learn more about Qlik Sense App Development and Usage.
I want to make a report that shows the current status of each account at the end of each period.
Unfortunately I do not have all the historical information. All I have is a bunch of records with ACCOUNT, DATE, ACCOUNT_TOTAL.
I am trying to create a table with: PERIOD, ACCOUNT, ACCOUNT_VALUE, where:
For example, if I have:
ACCOUNT, DATE, ACCOUNT_TOTAL
123, 20150212, 150
123, 20150816, 180
456, 20150110, 110
And I have:
I want to have a resulting table with:
PERIOD, ACCOUNT, ACCOUNT_VALUE
201503, 123, 125
201506, 123, 180
201509, 123, 180
201512, 123, 180
201503, 456, 110
201506, 456, 110
201509, 456, 110
201512, 456, 110
Any ideas on how could I make it?
I already autogenerated a table with the periods of analysis, now I need to populate with the latest value of each account for that period
Go to Solution.
Not sure why ACCOUNT 123 for PERIOD 201503 = 125 and not 150. Can you check this:
Date(Date#(DATE, 'YYYYMMDD')) as DATE,
123, 20150317, 125
Date(If(ACCOUNT = Previous(ACCOUNT), Peek('DATE') - 1, Today())) as ENDDATE
Order By ACCOUNT, DATE desc;
DROP Table TABLE1;
LOAD Date(MonthStart(Date#(PERIOD, 'YYYYMM')), 'YYYYMM') as PERIOD
TY. It worked perfectly.
The reason for reducing the value (and I made sure it was in the data set example) is that sometimes some credits can reduce the value of the account temporarly.
Thank you again.