topic Re: Urgent Help on Set analysis in QlikView

Urgent Help on Set analysis

Kushal_Chawda — Thu, 18 Jul 2013 06:23:24 GMT

Dear All,

I have filter of Year & Month... I have requirement like when I click on Year, for the corresponding month I should get previous 3rd month amount without selecting month.

For eg. when I select year 2013 I have four months Jan, Feb , Mar , Apr.. so for Jan I should get previous 3rd month amount( i.e Amount for Oct-2012)

Also I want to do it all the calculation on front end only as data model is freezed.

I dont have MonthYear or Date field created from backend, otherwise it was simple to implement.

I am using following setanalysis expression & dimenstion is

Dimension = Daily_Sales_Fin_Month_Name

Expression :

=sum({$<Daily_Sales_Fin_Month_Name={'$(=upper(date(AddMonths(date(date#(Daily_Sales_Fin_Month_Name&Daily_Sales_Comm_Fin_Year,'MMMYY'),'MMM-YY'),-3),'MMM')))'}>}BookSize_Debtor_AR_POS)

But I am not able to get the data using this expression. I treid a lot but failed to get the data.

Can anyone please help on this?

Please find the attached application with testdata.

Re: Urgent Help on Set analysis

Thu, 18 Jul 2013 06:44:24 GMT

Try this...

sum({$<Daily_Sales_Fin_Month_Name={'$(=upper(Date(MonthEnd(AddMonths(Max(Date(date#(Daily_Sales_Fin_Month_Name,'MMMYY')),-3)),'MMM') ))'}>}BookSize_Debtor_AR_POS)

Re: Urgent Help on Set analysis

Kushal_Chawda — Thu, 18 Jul 2013 06:52:08 GMT

thanks for your quick reply but this expression is not working for me..

have you tried with the qvw which i have aatched ?

Re: Urgent Help on Set analysis

Sokkorn — Thu, 18 Jul 2013 06:57:08 GMT

Hi Kush,

I would suggest to create a new field called MonthNum. The reason we can easy point in time

LOAD *,
    Num(Month(Date#(Daily_Sales_Fin_Month_Name,'MMM')))     AS [MonthNum];
LOAD * Inline [
Daily_Sales_Comm_Fin_Year,Daily_Sales_Fin_Month_Name,BookSize_Debtor_AR_POS
2012,APR,100
2012,MAY,50
2012,JUN,150
2012,JUL,175
2012,AUG,200
2012,SEP,210
2012,OCT,220
2012,NOV,130
2012,DEC,90
2013,JAN,100
2013,FEB,180
2013,MAR,300 ];

Now create Straight Table with

1. Dimension: Daily_Sales_Fin_Month_Name

2. Expression: Sum({$<MonthNum = {$(=Max(MonthNum)-3)}>} BookSize_Debtor_AR_POS)

See sample attached file.

Regards,

Sokkorn

Re: Urgent Help on Set analysis

Kushal_Chawda — Thu, 18 Jul 2013 07:50:40 GMT

Hi,

Thanks for your reply but your solution is not working for me.

Please see the output i need .

Re: Urgent Help on Set analysis

Kushal_Chawda — Thu, 18 Jul 2013 07:52:48 GMT

Please see the attched screenshot for the output i need

Re: Urgent Help on Set analysis

qlikpahadi07 — Thu, 18 Jul 2013 18:56:31 GMT

May b this Help you ....

you need to hard code as you said you can edit so i have changed your Month Jan, Feb, ... to 1 ,2,3,4 ...

I have created a Variable :

vMaxYearMaxMonth = =$(=max({<Daily_Sales_Comm_Fin_Year={$(=max(Daily_Sales_Comm_Fin_Year))}>} Daily_Sales_Fin_Month_Name))

this will give you current Month as data is dummy

simply need add the expression like

=if(vMaxYearMaxMonth = 1,

sum({<Daily_Sales_Comm_Fin_Year = {$(=max(Daily_Sales_Comm_Fin_Year)-1)},

Daily_Sales_Fin_Month_Name = {10}>} BookSize_Debtor_AR_POS),

if(vMaxYearMaxMonth = 2,

sum({<Daily_Sales_Comm_Fin_Year = {$(=max(Daily_Sales_Comm_Fin_Year)-1)},

Daily_Sales_Fin_Month_Name = {11}>} BookSize_Debtor_AR_POS),

if(vMaxYearMaxMonth = 3,

sum({<Daily_Sales_Comm_Fin_Year = {$(=max(Daily_Sales_Comm_Fin_Year)-1)},

Daily_Sales_Fin_Month_Name = {12}>} BookSize_Debtor_AR_POS),

.... add upto

if(vMaxYearMaxMonth = 11,

if(vMaxYearMaxMonth = 12

)))

I have attached the Solution might help you

Re: Urgent Help on Set analysis

qlikpahadi07 — Fri, 19 Jul 2013 12:16:39 GMT

May b this will be helpful

Re: Urgent Help on Set analysis

qlikpahadi07 — Fri, 19 Jul 2013 12:22:22 GMT

Also if you need to work dynamic for Month Selction just use

{1}

so expression will change to

Aggr(above( sum( {1}BookSize_Debtor_AR_POS),3),YearMonth)

Re: Urgent Help on Set analysis

Kushal_Chawda — Fri, 19 Jul 2013 12:52:32 GMT

Hi Rohit,

It works well for now. let me check for the original data.

thanks fella