topic Re: Compare this year data vs last year data in QlikView

Compare this year data vs last year data

Vinni2000 — Fri, 26 May 2023 12:52:27 GMT

I have invoice date '20200831' format in purchase order data so now i want to compare thise year purchase amount vs last year purchase amount by months using bar chart .

Re: Compare this year data vs last year data

Vinni2000 — Mon, 29 May 2023 15:28:20 GMT

i want my bar chart like this like i want to compare last 12 months data with previous 12 months data

Re: Compare this year data vs last year data

Kushal_Chawda — Tue, 30 May 2023 10:48:07 GMT

@Vinni2000 First create Calendar using InvoiceDate which contains Year & Month calculated using Invoice Date. Then create Bar chart with two dimension, 'Group' Month and 'Bars' as Year. Use below expression

Sum({<Year = {">=$(=max(Year)-1)"}>}Amount)

Re: Compare this year data vs last year data

Vinni2000 — Tue, 30 May 2023 11:22:03 GMT

Yeah I did the same and created month and year field from invoice create date using month() and year() then used same measures but i don't want that i want to compare this last 12 months means 2022 june to 2023 May with previous last 12 months means 2021 june to 2022 May

Re: Compare this year data vs last year data

Kushal_Chawda — Tue, 30 May 2023 14:27:51 GMT

@Vinni2000 Considering you have calendar on required date. Create a Flag like below in Calendar

Calendar: LOAD *,monthname(Date) as MonthYear, if(Date>= monthstart(addmonths(Today(),-11)) and Date <=Today(),'Last12Months', if(Date>= monthstart(addmonths(Today(),-23)) and Date<= monthend(addmonths(Today(),-12)) ,'PreviousLast12Months')) as Flag; Month: Load Distinct Month ,MonthYear Resident Calendar where Flag='Last12Months'; month_sort: Load Month, RowNo() as Sort_month Resident Month Order by MonthYear; Drop Table Month;

Create a bar chart with Dimension Month and below expression

Last12Month: Sum({<Flag={"Last12Months"}>}Sales) Expression label: =text(monthname(min({<Flag={"Last12Months"}>}Date)))&'-'&text(monthname(max({<Flag={"Last12Months"}>}Date))) PreviousLast12Month: Sum({<Flag={"PreviousLast12Months"}>}Sales) Expression label: =text(monthname(min({<Flag={"PreviousLast12Months"}>}Date)))&'-'&text(monthname(max({<Flag={"PreviousLast12Months"}>}Date)))

Sort your Month Dimension by Expression =Sort_month