topic Re: Need to Get current year and previous month data in QlikView

Need to Get current year and previous month data

khanashique — Tue, 27 Mar 2018 05:59:37 GMT

Hi Experts,

I need to see the sales for current year's previous month's data (this works only if i apply filter gauge and then selects the particular month), how to write a correct expression.

By giving below expression i can get the current years data but when i add months expression it gets failed.

Sum({<Year={'2018'}>}YTD_Sales) --> getting current year sales.

Sum({<Year={'2018'},Month={Max(Month)-1)}>}YTD_Sales) --> expression gets failed does not return any value.

Have tried another expression but no result:

Sum({<Year={'2018'},Month={Month(AddMonths(Now()),-1)}>}YTD_Sales) --> expression gets failed does not return any value.

Thanks,

MAK.

Re: Need to Get current year and previous month data

shiveshsingh — Tue, 27 Mar 2018 06:11:50 GMT

Try this

Sum({<Year={'2018'},Month={"$(=Month(AddMonths(Now(),-1)))"}>}YTD_Sales)

Re: Need to Get current year and previous month data

vardhancse — Tue, 27 Mar 2018 06:14:35 GMT

Hi,

can try this for previous month:

Month(AddMonths(Max(Date),-1))

**Date is is your date field you can replace with your actual date field

Re: Need to Get current year and previous month data

khanashique — Tue, 27 Mar 2018 06:29:45 GMT

Thanks Shivesh, It worked...

Re: Need to Get current year and previous month data

khanashique — Tue, 27 Mar 2018 06:33:23 GMT

Hi Sasi,

Thanks for your reply --> Month(AddMonths(Max(Date),-1)) did not worked...

The expression which worked is below that is provided by Shivesh

Sum({<Year={'2018'},Month={"$(=Month(AddMonths(Now(),-1)))"}>}YTD_Sales)

Regards,

MAK.

Re: Need to Get current year and previous month data

shiveshsingh — Tue, 27 Mar 2018 06:49:39 GMT

Glad to know it worked

Re: Need to Get current year and previous month data

vardhancse — Tue, 27 Mar 2018 08:27:15 GMT

Hi,

I meant replace Date with your date field and then this expression will work.

If you place Date column which is there not there in your data mart then it will not work.

However above expression mentioned correct is correct is correct but it's hard coded, but if we change the month in filter then it may not show the correct value

Re: Need to Get current year and previous month data

khanashique — Tue, 27 Mar 2018 08:33:45 GMT

Hi Thanks once again,

Yes, i have replaced the Date to my Month Field and it did not worked, yes expression which was provided by Shivesh does not change any value if the month filter is selected...

But the purpose of calculation in expressions solved in summary... (so the expression provided by Shivesh works fine.)

Thanks,

MAK