topic Re: Need Help in QlikView

Need Help

narender123 — Thu, 07 Nov 2013 10:19:03 GMT

Hi All,

I have made a calender which is based on "receipt data" of a table.

There is a paid date also.

receipt date paid date amount

1-1-2012 5-1-2012 100

25-2-2012 5-3-2012 200

20-2-2012 26-2-2012 288

--------

I want to calculate amount where receipt date and paid date both are of the same month.

for those where receipt date and paid date are not belong to same month then not show there amount.

if i select september month then only show those amount whose paid date and receipt date fall in the same month.

Please suggest me to sort out this.\

Thanks in Advance.

Narender

Re: Need Help

swuehl — Thu, 07 Nov 2013 10:24:54 GMT

Maybe something like

=sum( if(monthstart([receipt date]) = monthstart([paid date]), amount))

or create a flag in your data model table for that and just filter that flag.

Re: Need Help

narender123 — Thu, 07 Nov 2013 10:29:43 GMT

Thanks for the reply Swuehi.\

I will try your 1st expression

for 2nd option can you give me the example as i shown in my question to achieve flag?

Thanks.

Re: Need Help

israrkhan — Thu, 07 Nov 2013 10:31:09 GMT

Try Like below:

Sum({<[receipt date]= {>=$(=MonthStart([receipt date])) ,<= $(=MonthEnd([receipt date])), [paid date]= {>=$(=MonthStart([Paid date])) ,<= $(=MonthEnd([paid date]))}>} Amount)

Re: Need Help

swuehl — Thu, 07 Nov 2013 10:39:19 GMT

Assuming all three fields are within a single table in your data model:

LOAD *,

if(monthstart([receipt date]) = monthstart([paid date]), 1,0) as SameMonthFlag

INLINE [

receipt date, paid date, amount

1-1-2012, 5-1-2012, 100

25-2-2012, 5-3-2012, 200

20-2-2012, 26-2-2012, 288

];

Then in your chart:

sum({<SameMonthFlag = {1}>} amount)

sum( SameMonthFlag * amount)

Re: Need Help

narender123 — Mon, 11 Nov 2013 06:19:56 GMT

Hi Swuel,

Its not working for me .Its showing "_" means not working..There is change in my example and the change is that receipt date and paid date fields belongs to different table and joining between them via doc_id.

Thanks.

Narender

Re: Need Help

narender123 — Mon, 11 Nov 2013 06:46:31 GMT

Hi Israrkhan,

Its not working ,it is showing error with RED line in expression .

Thanks.

Re: Need Help

Anonymous — Mon, 11 Nov 2013 08:26:26 GMT

" I want to calculate amount where receipt date and paid date both are of the same month."t you want and i

if this is want and receipt date and paid date and amount all are of same table

create a flag like this in script (in your table)

if (num(month(paiddate)) = num(month(receiptdate)),1,0 ) as Flag

then use expression like this sum(if(Flag=1, Amount) or if (Flag=1, sum(Amount))

anant

Re: Need Help

swuehl — Mon, 11 Nov 2013 10:01:01 GMT

Then please upload an updated sample of your data (i.e. the two or more tables involved, incl. some lines of sample data). What's the relationship between the two tables? 1:1 or 1:n?

Re: Need Help

narender123 — Mon, 11 Nov 2013 12:39:40 GMT

Thanks Swuehl.

i have used this expression.

=if(monthstart(Date) = monthstart(event_date),sum({<REGIME={'C*'} ,TAX_CODE={'VT'} >}TAX*-1))

But its showing "_".

I have also tried with this:

=sum(if(monthstart(Date) = monthstart(event_date),sum({<REGIME={'C*'} ,TAX_CODE={'VT'} >}TAX*-1)))

Its also showing same "_".

Suggest me to sort out this.

Thanks

Re: Need Help

narender123 — Mon, 11 Nov 2013 12:41:21 GMT

Thanks Anant but the receipt date and paid date are belong to different tables so i cant make flag.

So plaese advice .

Thanks

Re: Need Help

swuehl — Mon, 11 Nov 2013 12:49:02 GMT

i have used this expression.

=if(monthstart(Date) = monthstart(event_date),sum({<REGIME={'C*'} ,TAX_CODE={'VT'} >}TAX*-1))

But its showing "_".

It's almost never a good idea to use an aggregation inside an if statement if you want to filter your records you want to aggregate,

Your second version uses an aggregation inside an aggregation which is not allowed without using advanced aggregation (aggr() function).

try:

=sum({<REGIME={'C*'} ,TAX_CODE={'VT'} >} if(monthstart(Date) = monthstart(event_date), TAX*-1))