topic Re: using set analysis in QlikView

using set analysis

Tue, 25 Dec 2012 07:14:27 GMT

Dear members,

i have a sample data

requirement is if i select any year or month bar graph will show three bars

one bar current month

sec bar prev month

third bar last year curr month values comp sector wise fin calender

Re: using set analysis

er_mohit — Tue, 25 Dec 2012 07:32:29 GMT

for this you have to use aggr function to see the graph comparision between months

for last yr currnt selection

( {$<month = {$(=Only(year)-1)}>} sector)

you can add another expression for last year as by use of variable vlast year so it look like

( {$<month = {$(vLastYear)}>} sector )

for previous month

{$(only(month)-1)}

for current month

{$(only(month))}

hope it helps you....

Re: using set analysis

nirav_bhimani — Tue, 25 Dec 2012 08:30:27 GMT

Hi,

First you have to prepare Master Calendar which consist of year, month n all.

Add dimension as Sector.

Then add this 3 Exp.

Current Month :

=sum({<CalendarYear={$(=max(CalendarYear))},CalendarMonthName={$(=Month(Max(Transaction_Date)))}>} OS_Approval)

Previous Month:

=sum({<CalendarYear={$(=max(CalendarYear))},CalendarMonthName={$(=month(addmonths((max(Transaction_Date)),-1)))}>}OS_Approval)

Last year current month values:

sum ({ <Transaction_Date= P( { <Transaction_Date={">=$(=(Max(Transaction_Date)-365) ) <= {<CalendarYear={$(=max(CalendarYear))},CalendarMonthName={$(=Month(Max(Transaction_Date)))}>}"}>} ) >} OS_Approval)

Hope this will help you.

Regards,

Nirav Bhimani

Re: using set analysis

Tue, 25 Dec 2012 09:12:49 GMT

thanks for replay

and wt is mean by p for last exp

Re: using set analysis

jagan — Tue, 25 Dec 2012 10:41:11 GMT

Try this expressions

For current month selected

=Sum({<Year=, Month=, DateField={'>=$(=MonthStart(Max(DateField)))<=$(=MonthEnd(Max(DateField)))'}>} Sales)

Previous Month

=Sum({<Year=, Month=,DateField={'>=$(=MonthStart(Max(DateField), -1))<=$(=MonthEnd(Max(DateField), -1))'}>} Sales)

Last year current month

=Sum({<Year=, Month=,DateField={'>=$(=MonthStart(Max(DateField), -12))<=$(=MonthEnd(Max(DateField), -12))'}>} Sales)

Hope this helps you.

Regards,

Jagan.

Re: using set analysis

nirav_bhimani — Tue, 25 Dec 2012 10:41:29 GMT

Hi,

P means All possibility.

All the above expression are working hope this will help you.

Ur problem has been resolved?

Regards,

Nirav Bhimani

Re: using set analysis

Tue, 25 Dec 2012 11:04:15 GMT

thanks but last one is not working i have fin cal here

cani send my app

Re: using set analysis

nirav_bhimani — Tue, 25 Dec 2012 11:27:12 GMT

Hi Ram,

Please share your QVW file.

Regards,

Nirav Bhimani

Re: using set analysis

Tue, 25 Dec 2012 11:41:57 GMT

hai

Re: using set analysis

nirav_bhimani — Tue, 25 Dec 2012 12:13:18 GMT

Hi,

Try this.

=Sum({<Transaction_Date={'>=$(=MonthStart(Max(Transaction_Date), -12))<=$(=MonthEnd(Max(Transaction_Date), -12))'}>} Repay)

Hope your problem is resolve.

Regards,

Nirav Bhimani