how to show the previous month last day Sales as current month Starting day Sales

[venkatbza]

Hi Friends,

I am facing a problem to get the previous month last day sales as current month Starting Sales

i nee to show the data in straight table like mentioned below.

Start values should be the last day sales of the previous month. i have tried to implement with Below() function  it is given the below value for the upper column but the problem is if i select 2017 in a list box Jan-2017 start should show the Dec-2016, but is it is showing Null may be technically it is belong to 2016 but i need to show that in 2017 Jan staring sales.

help if any one have the answer.

Desired Output:-

Month Year	Start	sales	Expanses	Month end or Till date
Jun-2017	125	20	-15	130
May-2017	122	10	-7	125
Apr-2017	115	15	-8	122
Mar-2017	105	20	-10	115
Feb-2017	99	10	-4	105
Jan-2017	100	5	-6	99
Dec-2016	110	10	-20	100
Nov-2016	0	120	-10	110

Current output:-

Month Year	Start	sales	Expanses	Month end or Till date
Jun-2017	130	20	-15	130
May-2017	125	10	-7	125
Apr-2017	122	15	-8	122
Mar-2017	115	20	-10	115
Feb-2017	105	10	-4	105
Jan-2017	99	5	-6	99
Dec-2016	100	10	-20	100
Nov-2016	0	120	-10	110

Thanks,

Venkat

[Anil_Babu_Samineni]

From given data, If you select the Feb-2017 then what does the output come?

[venkatbza]

Hi Anil,

If i select Feb-2017 in the filter now it is showing 0 in my qlikview report. but it needs to show END of the Jan value 99.

i would like to show the data like give table in qlikview.

[Anil_Babu_Samineni]

Like this? when we are select Feb-2017

[venkatbza]

Hi Anil,

Sorry to confuse you.

Like below

What ever i posted in the question that is required out put.

[Anil_Babu_Samineni]

May be this? Hope this helps

I've changed

Sum({<[Month Year] = {"$(=Date(Max([Month Year]-1),'MMM YYYY'))"}>} [Month end or Till date])

[venkatbza]

Thanks for your reply Anil.

But this is not what i am expecting.

Month year is only my dimension in the chart, remaining all are expressions. like below.

sum (start ) ---for sum of start we derived from last day sales of last month

sum (sales )

sum (Expanses)

and how month end or till date are calculate is (sum (start )+sum (sales )-sum (Expanses))

for sum of start we derived from last day sales of last month

for instance:-

My December-2016 sales is like below table. in the below table start 110 is derived from last day of Nov-2016

And sales is sum of Dec-2016 sales, and Expanse is sum of Dec-2016 Expanses.

what ever the end of Dec-2016(Month end or till date) sales should be the start of Jan-2017 and + Jan sales - Expanses should be the month end or till date sales.

i am able to find the logic for Month end or till date. but unable to find the solution for start.

Month Year	Start	Sales	Expanses	Month end or till date balance
Dec-2016	110	10	-20	100
Nov-2016	0	120	-10	110

Month Year	Start	Sales	Expanses	Month end or till date balance
Jan-2016	100	5	-6	99

Thanks,

Venkat

[venkatbza]

Thanks for your reply Anil.

But this is not what i am expecting.

Month year is only my dimension in the chart, remaining all are expressions. like below.

sum (start ) ---for sum of start we derived from last day sales of last month

sum (sales )

sum (Expanses)

and how month end or till date are calculate is (sum (start )+sum (sales )-sum (Expanses))

for sum of start we derived from last day sales of last month

for instance:-

My December-2016 sales is like below table. in the below table start 110 is derived from last day of Nov-2016

And sales is sum of Dec-2016 sales, and Expanse is sum of Dec-2016 Expanses.

what ever the end of Dec-2016(Month end or till date) sales should be the start of Jan-2017 and + Jan sales - Expanses should be the month end or till date sales.

i am able to find the logic for Month end or till date. but unable to find the solution for start.

Month Year	Start	Sales	Expanses	Month end or till date balance
Dec-2016	110	10	-20	100
Nov-2016	0	120	-10	110

Month Year	Start	Sales	Expanses	Month end or till date balance
Jan-2017	100	5	-6	99

Thanks,

Venkat

[Anil_Babu_Samineni]

I am out of range now. Let's wait mrkachhiaimp his response

[MK_QSL]

I usually don't work on questions without sample data or sample application.

venkatbza‌Please provide sample data according to your application and requirements.