topic Simple Question in QlikView

Simple Question

Mon, 08 Aug 2011 07:46:35 GMT

Hi Guys:

I have a very simple question but not able to get the answer hope that you guys can help.

In Qlikviw when we select the Year, Month and Day for sale we will be getting the transaction for that particular day. For example: - Year 2011 Month 8 and Day 8 we will get the transaction $200.

If we want the sale from 1-8 of August which is the beginning of the month to the selected day. How can I do that?

Sorry for such a simple question and hope that the solution will be simple too by the way I can not use the set analysis function.

Thanks Guys

Simple Question

lironbaram — Mon, 08 Aug 2011 08:20:03 GMT

hey

simple Question

cant the user just select days 1-to 8

that will give you the sum

then you can create a varaible

vMaxDate : =max(DateField)

and put another expression :

Sum(if(DateField=$(vMaxDate),Transactions)) to get The Sales in The maxium Date

Simple Question

Mon, 08 Aug 2011 08:28:49 GMT

Hi Baram:

Thanks for your quick reply.

The old report work like this:-

When the user selection a particular date e.g. 8/8/2011 the figure will show 1-8 /8/2011 the sum for 1 to 8.

The vMaxDate : =max(Transdate)

Will have to be written in the edit script right.

And the Sum (if (DateField =$(vMaxDate), Transactions))

Will have to in the Expression right?

I am very new to Qlikview.

Thanks again Baram

Simple Question

lironbaram — Mon, 08 Aug 2011 09:05:02 GMT

hei

can you post a sample data

this way it'll be easy to understnad and answer all the questions

Simple Question

Mon, 08 Aug 2011 09:13:58 GMT

but rich,

The set expression for transaction calculation need to be generic rite?

That is if no date as such is selected ..say, only year 2011 and month 8 is selected..do you expect to see the aggregated sum of transactions for the month 1 to 8 ?

Simple Question

Mon, 08 Aug 2011 09:15:32 GMT

Hi,

You can do this using set analysis expression. Create two variables as follows,

1. v_Start_Date - Start date of the selected year month (01-Aug-2011)

2. v_End_Date - selected date (08-Aug-2011)

Use the following expression in the calculation,

[code]

SUM($<[DateField] = {">=$(=$(v_Start_Date))<=$(=$(v_End_Date))"}>Transactions)

[/code]

This will give you the sum of the transactions from the start date of the month till the selected date. Hope this helps

Simple Question

Mon, 08 Aug 2011 09:38:18 GMT

Hi Haneesh:

I am not able to use set analysis because I am using qlikview 7.5.

Qlikview 7.5 does not support set analysis.

Hi Sircute:

Even if the user select say year 2011 and month 8 the user must also select day of the month. Once selected the Year, Month and Day. The report will show the date transaction and not that day transaction. I am not sure is that call Month to Date.

Hi All:

Here I the sample:-

Hope that it makes sense.

Simple Question

Mon, 08 Aug 2011 09:48:43 GMT

Sorry guys I think the update is no helpful at all.

I will give the example as below:-

Crate To(+) Crate From

1/8/2011 5 10

2/8/2011 20 20

3/8/2011 30 40

If user chose date 1/8/2011 the Crate To and Crate From will show 5 and 10

But if user choose 2/8/2011 To and Crate From should show 25 and 30.

If user choose 3/8/2011 To and Crate From should show 55 and 70.

But Qlikview only show 20 and 20 which is that day transaction on 2/8/2011

And 30 and 40 for 3/8/2011.

I know using set analysis is easy to get it right but I am not able to use Set Analysis as I am using Qlikview 7.5.

Thanks all for your kind help.

Simple Question

qliksus — Mon, 08 Aug 2011 10:18:14 GMT

Hi ,

If ur tableA is having columns Dates,Crate to,Crate from

Create another table like

Tableb:

Load Dates as Dates1 resident tableA ;

Now show this Dates1 column in the list box for the selection and in the chart

Give expression like

sum(if( Dates >= monthstart(Dates1) and Dates <= max(Dates1) , Crate to)

sum(if( Dates >= monthstart(Dates1) and Dates <= max(Dates1) , Crate from)

Now as your column Dates1 is not related to anything it will be passed as values for the

Condition u are giving

Hope this solve ur issue

Simple Question

Mon, 08 Aug 2011 12:42:27 GMT

Hi Richard,

I agree with qliksus's, solution on this, though the expression would need a little tweaking... if the user selects more than one dat.. the expression wont work.

the following expression would display correctly

sum(if( Dates >= monthstart(max(Dates1)) and Dates <= max(Dates1) , Crate to)

sum(if( Dates >= monthstart(max(Dates1)) and Dates <= max(Dates1) , Crate from)

we'll have to use the max function under monthstart() so as to select the maximum of the selected date.

Mind you, this might display some value even if there are no selected date.. but for that you can set a calculation condition for the chart, like "GetSelectedConut(Dates1)>0"

Rgrds,

Abhinava

Simple Question

Wed, 10 Aug 2011 06:12:00 GMT

Hi Qliksus and Abhinava and Guys:

Thanks for both of your solution.

I have a small issue with the Expression that both suggestions.

My Create From Expression is:-

sum (if (invtype='40',goodqty)) + sum (if (invtype='20',goodqty))

And Crate To Expression is :-

sum (if (invtype='42',goodqty)) + sum (if (invtype='34',goodqty))

I am trying to put these expressions into the Edit script but it does not seem to be getting it correct.

So that I will be able to get CrateFrom and CrateTo in order to use the expression that you suggested.

Any idea in putting these expressions into the Edit script?

Sorry for the late reply guys…

Simple Question

Wed, 10 Aug 2011 08:26:35 GMT

Hi richard,

i believe sum() function does not work in script.. I m checking on examples how to implement ur logic.

Rgrds,

Abhinava

Simple Question

Wed, 10 Aug 2011 09:08:11 GMT

Hi Abhinava:

That is what I was thinking because no matter how I try in the script with Sum function and also Sum(if) function it just won't work.

Thanks you for your reply.

Will wait for your good news

Simple Question

Wed, 10 Aug 2011 09:21:41 GMT

Hi richard,

Well gud news is that sum does work in script! you'l hv to use the "group by" clause on the field you want the sum to be calculated by....

I think you should implement the sum on front end rather than in script..

Rgrds,

Abhinava

Re: Simple Question

Wed, 10 Aug 2011 09:24:17 GMT

Hi,

Please see the attached sample may help you.

Cheers.

Simple Question

Wed, 10 Aug 2011 09:50:50 GMT

Hi Abhinava and BlackRockS:

Thanks fo your reply.

If i can not inculde the sum inside the script but has to do it in the front end.

Can I use the following Expression?

Sum(if(Date<=SelDate,CrateFrom))

sum (if (invtype='40',goodqty)) + sum (if (invtype='20',goodqty))

To As follows:-

Sum(if(Year = year(CurrentDate) and Month1 = month(CurrentDate)

and [Inv. Date] <= CurrentDate, (invtype='40',goodqty)) + sum (if (invtype='20',goodqty))

It seems like there is something wrong with the following Expression...

Headach man....

Simple Question

Wed, 10 Aug 2011 10:05:00 GMT

Hi,

See if this can help you.

Sum(if(Year = year(CurrentDate) and Month1 = month(CurrentDate) and [Inv. Date] <= CurrentDate and (invtype=40 or invtype=20),goodqty))

Cheers.

Simple Question

Wed, 10 Aug 2011 10:17:42 GMT

Hi Richard,

i think wht u r trying to do is this...

Sum(if((Year = year(CurrentDate)) and (Month1 = month(CurrentDate)) and ([Inv. Date] <= CurrentDate) and (invtype='40' or invtype='20'),goodqty))

Rgrds,

Abhinava

Simple Question

Thu, 11 Aug 2011 04:55:42 GMT

Hi BlackRockS, Abhinava and Guys:

You all ROCKS and very helpful persons.

I never expect such a good reply from you guys… Once again you thank you guys.

The following should be written in the Expression right?

Sum(if((Year = year(CurrentDate)) and (Month1 = month(CurrentDate)) and ([Inv. Date] <= CurrentDate) and (invtype='40' or invtype='20'),goodqty))

And for the Year and Month I should write in the Edit Script right?

The following is from my script for the Year and Month.

Please comment if I got it wrong:-

LOAD

transdate,

Day(transdate) as Transday,

Year(transdate) as Year,//Because of syn$

month (transdate) as monthdes, //will be in Jan,Feb, Format

num(Month(transdate)) as month,

num(Month (transdate)) as Month1,

invtype,

goodqty,

May I know how to get the Year and year and also the Month1 and month.

For the [Inv.Date] I should be using [transdate] right?

Sorry to ask so many simple questions.

Simple Question

Thu, 11 Aug 2011 05:17:33 GMT

Hi Richard,

i didnt get your question...

"May I know how to get the Year and year and also the Month1 and month?"

For the [Inv.Date], you should probably use [transdate]. you should check in the source what dates fields are avalaible.

Cheers,

Abhinava

P.S- thanx for the accolades