topic Re: Current and Previous Month Sales Comaprison in QlikView

Current and Previous Month Sales Comaprison

Anonymous — Mon, 06 Jan 2014 18:50:11 GMT

Hi Friends,

I have one problem ,my client requirements is sales report on basis of date filed ,suppose i selected Jan-2013

the current Month is Jan-2013 and Previous month is Dec-2012.

i needed to display report structure like.

Date	Sales For Jan-2013	Sales For dec-2012
01-01-2013	2000	3000
02-01-2013	2500	4000
03-01-2013	2200	5000
04-01-2013	2100	2500
05-01-2013	5000	4500

what ever the Dec-2012 value are related to Dec month 01-12-2012,02-12-2012,03-12-2012,04-12-2012,05-12-2012.

same like for every month.

Please help me how to resolve the issue.

Thanks for your support.

Please find the test data for creating report,i want to calculate in UI only not is scripting part.

Thanks & Regards

SHAIK

Re: Current and Previous Month Sales Comaprison

Mon, 06 Jan 2014 20:50:15 GMT

I would recommend using a Day field instead of the Date field you have above to avoid confusing your user. I have attached an example using variables to define the date range.

Re: Current and Previous Month Sales Comaprison

Anonymous — Tue, 07 Jan 2014 06:56:21 GMT

Hi Rebecca,

Thanks for your replay,here problem is user is asking with Date field dimensions.

Please let me know if it is possible or not,because i want to calculate UI only not in back end scripting.

Thanks
SHAIK

Re: Current and Previous Month Sales Comaprison

Anonymous — Tue, 07 Jan 2014 07:04:42 GMT

Instead of Day you can use the Date field .

It will definitely work.

Re: Current and Previous Month Sales Comaprison

Anonymous — Tue, 07 Jan 2014 07:26:59 GMT

Hi Nitin,

thanks for your support i tried for that one i did't get the required answer, can you please provide the test application.

above i shared the test data,

Thanks

SHAIK

Re: Current and Previous Month Sales Comaprison

VishalWaghole — Tue, 07 Jan 2014 07:52:39 GMT

Hi Khadar,

First go to edit script and cerate MonthName field as

MonthName(INVOICE_DT) as InvDtMonthName

then try this exression in your chart

= sum ({<Date = {"=$(=AddMonths(max(InvDtMonthName),-1))"}>}Sales)

Hope it will work for you.

- Regards,

Vishal Waghole

Re: Current and Previous Month Sales Comaprison

tresB — Tue, 07 Jan 2014 08:19:41 GMT

Dear Shaik,

To resolve this sort of requirement, it is usually taken into the script and followed a well known AsOfDate way.

Doing it in the front end, would be a bit complex. Please see the attached sample.

Re: Re: Current and Previous Month Sales Comaprison

Anonymous — Tue, 07 Jan 2014 08:29:14 GMT

PFA.

Re: Current and Previous Month Sales Comaprison

Anonymous — Tue, 07 Jan 2014 08:44:49 GMT

Hi vishal,

thanks for your replay,i tried what ever the expression you are given,it's not working according to requirement.

if you have any other clue please let me know.

Thanks

SHAIK

Re: Current and Previous Month Sales Comaprison

sanketkhunte — Tue, 07 Jan 2014 09:10:35 GMT

Hi Shaik

Tell me which version of Qlikview you are using ?

If you are using 11 or onwards (SR) just create ALTERNATE STATE for current month and previous month : Check these sets in expression.

For more reference : User "What's New in Qlikview 11 - Comparative Analysis point"

Hope this will help.

Thank you

Sanket

Re: Current and Previous Month Sales Comaprison

Anonymous — Tue, 07 Jan 2014 09:26:27 GMT

Hi tresesco,

thanks for your support,it's working properly, but i needed another help,if i have sale is 0 then i want to take above sales value where value is available,i am sharing excel file,i added yellow color.

really thanks for your support,

Thanks

SHAIK

Re: Re: Current and Previous Month Sales Comaprison

Anonymous — Tue, 07 Jan 2014 09:28:07 GMT

Hi tresesco,

really thanks for your support,

please find the attachment of Excel file.

Thanks

SHAIK

Re: Re: Current and Previous Month Sales Comaprison

Anonymous — Tue, 07 Jan 2014 09:29:05 GMT

Hi Sanket,

Thanks for Your Support,currently i am using 11.2 version,

Thanks

SHAIK

Re: Current and Previous Month Sales Comaprison

sanketkhunte — Tue, 07 Jan 2014 09:39:16 GMT

Great !!

So you must have aware about Alternate State for Comparative Analysis.

Re: Current and Previous Month Sales Comaprison

tresB — Tue, 07 Jan 2014 10:12:46 GMT

Try like:

If(Sales=0,Peek('NewSales'),Sales) as NewSales,

Re: Re: Current and Previous Month Sales Comaprison

Anonymous — Tue, 07 Jan 2014 10:20:07 GMT

Hi tresesco,

i want to calculate on UI only,not in back end script,peek will be work on back end only.please give the suggestion on Ui.

Thanks

SHAIK

Re: Current and Previous Month Sales Comaprison

tresB — Tue, 07 Jan 2014 10:28:09 GMT

In UI, single 0/null value could be filled using above()/below() functions. But with multiple contigous rows to be filled with previous value ! - have to work on.

Re: Re: Current and Previous Month Sales Comaprison

Anonymous — Tue, 07 Jan 2014 10:41:50 GMT

Hi tresesco,

i know that above() and below() it wont support for loops,because if i created back end ,lower level hierarchy it wont be work,it's need to be work means i want to go for ledger concept,thanks for your help.

please if you know any suggestions on UI,please let me know.

Thanks

SHAIK