topic Re: Ignore date selection (dimension field) in QlikView

Ignore date selection (dimension field)

Mon, 23 Feb 2015 11:05:51 GMT

Hi,

I need the below expression to ignore my selection in dimension field Date

Cannot get it to work properly for some reason, any suggestions?

//O

=If(sum(SoD_Pmt_amount) <> 0,

If(Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),4) = 0,
If(Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),3) = 0,
If(Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),2) = 0,

If(Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1) = 0,

Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),5),
Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1)),
Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1)),
Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1)),

If(Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),2) = 0
and
Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1) = 0,

Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),3),
Above(sum(if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1))))

Re: Ignore date selection (dimension field)

sunny_talwar — Mon, 23 Feb 2015 11:14:43 GMT

Try and see if this works:

Put {<Date = >} or {<Date = {'*'}>} after Sum everywhere you see Sum

Best,

Re: Ignore date selection (dimension field)

Mon, 23 Feb 2015 11:20:53 GMT

Tried this but getting the very same result.

=If(sum({<Date = >}SoD_Pmt_amount) <> 0,

If(Above(sum({<Date = >}if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),4) = 0,
If(Above(sum({<Date = >}if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),3) = 0,
If(Above(sum({<Date = >}if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),2) = 0,

If(Above(sum({<Date = >}if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1) = 0,

Above(sum({<Date = >}if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),5),
Above(sum({<Date = >}if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1)),
Above(sum({<Date = >}if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1)),
Above(sum({<Date = >}if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1)),

If(Above(sum({<Date = >}if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),2) = 0
and
Above(sum({<Date = >}if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1) = 0,

Above(sum({<Date = >}if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),3),
Above(sum({<Date = >}if(SoD_Pmt_Subtype='Credit line connected payment',SoD_Pmt_amount)),1))))

Re: Ignore date selection (dimension field)

sunny_talwar — Mon, 23 Feb 2015 11:22:57 GMT

Tested on our previous post's expression and it works:

Sum Previous Date

=If(Sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)) <> 0, If(Above(Sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),4) = 0,

If(Above(Sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),3) = 0, If(Above(Sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),2) = 0,

If(Above(Sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1) = 0, Above(Sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),5),

Above(Sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)), Above(Sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)),

Above(Sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)), If(Above(Sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),2) = 0 and

Above(Sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1) = 0,

Above(Sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),3), Above(Sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1))))

Sum Date

=sum({<Date = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount))

Best,

Re: Ignore date selection (dimension field)

sunny_talwar — Mon, 23 Feb 2015 11:25:06 GMT

Take at the application, may be you can figure out the difference. PFA

Best,

Re: Ignore date selection (dimension field)

Mon, 23 Feb 2015 12:03:14 GMT

problem occurs for the first date when you select a specific month. Pls see below:

Re: Ignore date selection (dimension field)

sunny_talwar — Mon, 23 Feb 2015 13:09:39 GMT

So the thing to note here is that by putting in {<Date = >} you are telling the expression not to change based on any selection in Date listbox. In your case you made a selection on Month-Year list box which will not stop the expression to change. To tell the expression not to change on Date and Month-Year selections you would put this:

{<Date = , Month-Year = >}

So now if there are any other fields that you don't want your expression to change on, put them there in your set analysis modifier.

This below expressions won't change on any selection in Date and Month-Year listbox

Sum Previous Date

=If(Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)) <> 0, If(Above(Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),4) = 0,

If(Above(Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),3) = 0, If(Above(Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),2) = 0,

If(Above(Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1) = 0, Above(Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),5),

Above(Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)), Above(Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)),

Above(Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)), If(Above(Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),2) = 0 and

Above(Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1) = 0,

Above(Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),3), Above(Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1))))

Sum Date

=Sum({<Date = , [Year-Month] = >}if(Pmt_Subtype='Credit line connected payment',Pmt_amount))

Output:

Value for 2014/11/28 Sum Previous Date is blank because the data starts from 2014/11/28, so there is no previous date value to display (i.e., 2014/11/27 value)

HTH

Best,

Re: Ignore date selection (dimension field)

Mon, 23 Feb 2015 13:16:48 GMT

Aaa, I see that explains.

In my case im generating a report for every month so I just wanna see the december dates.

Is there a way of just showing the selected month? For instance if I select December-2014 I wanna see all of the december dates only, but with the calculations intact, meaning that I wanna see the prev date sum för 1-dec too

Re: Ignore date selection (dimension field)

Mon, 23 Feb 2015 13:18:36 GMT

I wanna select December and see the below Only, no novermber dates.

Re: Ignore date selection (dimension field)

sunny_talwar — Mon, 23 Feb 2015 13:32:30 GMT

Yes there is a way to do that. I am working on it and will respond when I have the solution for you.

Best,

Re: Ignore date selection (dimension field)

sunny_talwar — Mon, 23 Feb 2015 13:57:00 GMT

This is what you can do. Create a variable and call it anything you want. I called it vRange

vRange =

='>=' & If(Sum({<Date = {'$(=Date(AddMonths(Max(Date(Date)),-1)))'}, Pmt_Subtype = {'Credit line connected payment'}, [Year-Month] = >} Pmt_amount) = 0,

If(Sum({<Date = {'$(=Date(AddMonths(Max(Date(Date)),-1)-1))'}, Pmt_Subtype = {'Credit line connected payment'}, [Year-Month] = >} Pmt_amount) = 0,

If(Sum({<Date = {'$(=Date(AddMonths(Max(Date(Date)),-1)-2))'}, Pmt_Subtype = {'Credit line connected payment'}, [Year-Month] = >} Pmt_amount) = 0,

If(Sum({<Date = {'$(=Date(AddMonths(Max(Date(Date)),-1)-3))'}, Pmt_Subtype = {'Credit line connected payment'}, [Year-Month] = >} Pmt_amount) = 0,

Date(AddMonths(Max(Date(Date)),-1)-4), Date(AddMonths(Max(Date(Date)),-1)-3)), Date(AddMonths(Max(Date(Date)),-1)-2)), Date(AddMonths(Max(Date(Date)),-1)-1)),

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

'<=' & Max(Date(Date))

And then use these two expressions:

Sum Date

=Sum(if(Pmt_Subtype='Credit line connected payment',Pmt_amount))

Sum Previous Date

=If(Sum({<Date = {'$(vRange)'} , [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)) <> 0, If(Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),4) = 0,

If(Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),3) = 0, If(Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),2) = 0,

If(Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1) = 0, Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),5),

Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)), Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)),

Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1)), If(Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),2) = 0 and

Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1) = 0,

Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),3), Above(Sum({<Date = {'$(vRange)'}, [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)),1))))

Output:

HTH

Best,

Re: Ignore date selection (dimension field)

sunny_talwar — Mon, 23 Feb 2015 13:58:14 GMT

Personally I would have liked to have another month of data to test if this is doing as you would want to do. If you can try and test it with January's data in it.

Best,

Re: Ignore date selection (dimension field)

Mon, 23 Feb 2015 14:48:06 GMT

I will try with some more months,

meanwhile i need the first statement to be covering all payments, not just credit line connected payment like the below: I managed to change it myself before but now it does not worrk.

Current:

=If(Sum({<Date = {'$(vRange)'} , [Year-Month] = >}If(Pmt_Subtype='Credit line connected payment',Pmt_amount)) <> 0

Should be something with:

IF(Pmt_amount)<>0 ONLY

This beacuse some days there are no credit line connected payments.

Re: Ignore date selection (dimension field)

Mon, 23 Feb 2015 15:09:19 GMT

never mind, I think I've fixwed it

Re: Ignore date selection (dimension field)

sunny_talwar — Mon, 23 Feb 2015 15:21:42 GMT

Great,

I got busy with stuff and was not able to respond. I am glad you got it all figured out.

Best,

Re: Ignore date selection (dimension field)

sunny_talwar — Mon, 23 Feb 2015 15:29:11 GMT

If everything is completed, I would suggest marking one of the answers as correct (If I have not provided the final solution, than put the final solution and mark it as correct), so that people can use it in the future.

Best,

Re: Ignore date selection (dimension field)

Mon, 23 Feb 2015 16:00:24 GMT

Hi,

actually, now that I'm trying with the jan data I can see it cannot handle the new year holidays, any ideas why?

Pls see the attached example file.

best regards,

Olle

Re: Ignore date selection (dimension field)

sunny_talwar — Mon, 23 Feb 2015 16:06:38 GMT

Let me take a peek at it.

Re: Ignore date selection (dimension field)

sunny_talwar — Mon, 23 Feb 2015 16:52:48 GMT

Like I mentioned in your other post on the community that the expression may not work when the holidays are 1 or 3. I have fixed that now. Upto 4 holidays, the expression will be able to handle all 0's. More than that, you will need a bigger if condition.

Not posting the expression here, because it is a huge one. Take a look at the application instead.

HTH

Best,

Re: Ignore date selection (dimension field)

Tue, 24 Feb 2015 09:09:18 GMT

Thanks sunindia!

Just realized one problem thuough.

As I think i mentioned earlier, some regular days there are not "credit line connected payments".

These should be showing like 0.

I tried to change the expression so that the "master" IF-statement would test Pmt_amount<>0 rather than

payment_amounts that are also pmt_subtyp = credit line connected payments.

But it does not work for me properly.

I created a new topic, can you pls have a look?

http://community.qlik.com/thread/153856

Thanks in advance,

Olle