topic Re: Dat in QlikView

Dat

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

HI ALl, PLease find the aatchment of the qvw file ..

I have a table ..and iam trying to get the days by substituting to date value but its not shoing the desired result for all the Numbers...

Can someon please help me over this

Tahins is advance

Re: Dat

swuehl — Sun, 27 Aug 2017 08:33:55 GMT

Remember:Use Aggregation Functions!

You won't get a result where there are multiple combinations of EndDate and LoadDate per ReleaseNumber

ReleaseNumber	EndDate	LoadDate	StartDate	EndDate-LoadDate

6.11	14-06-2017	31-05-2017	23-05-2017	14
6.11	14-06-2017	30-06-2017	23-05-2017	-16
6.11	14-06-2017	31-07-2017	23-05-2017	-47

So you need to use aggregation functions in your expression, don't know what is needed in your case maybe something like

ReleaseNumber	Max(EndDate)-Min(LoadDate)	Avg(EndDate-LoadDate)
	14	-16,333333
6.11	14	-16,333333

Then you will see all dimension values:

ReleaseNumber	Max(EndDate)-Min(LoadDate)	Avg(EndDate-LoadDate)
	398	-6,0948276
6.11	14	-16,333333
6.12	9	9
6.13	72	72
6.15	128	128
17.1	314	-27,263158
17.11	189	123,5
17.12	217	143,2
17.2	90	-59,764706
17.3	59	-43,555556
17.4	87	-30,846154
17.5	85	-15,642857
17.6	82	-7,4615385
17.7	80	8,8
17.8	138	38
17.9	105	64

Re: Dat

smilingjohn — Sun, 27 Aug 2017 08:46:04 GMT

Hi Stefan ,

If i add start date as dimensions then it shows me the correct days when i subtract EndDate from Load Date..

Now i need to Hide the Start date dimension ,,,,but iam using oivot table...

How do i deal with this ?

Any suggestion

Re: Dat

antoniotiman — Sun, 27 Aug 2017 09:50:57 GMT

Hi John,

as Stefan suggests, You need to use Aggregation Function, maybe

Sum(EndDate-LoadDate)

Regards,

Antonio

Re: Dat

swuehl — Sun, 27 Aug 2017 10:21:05 GMT

Even if you add StartDate as dimension, this won't help for ReleaseNumber that show different combinations of EndDate and LoadDate per StartDate:

ReleaseNumber	EndDate	LoadDate	StartDate	EndDate-LoadDate

6.11	14-06-2017	31-05-2017	23-05-2017	14
6.11	14-06-2017	30-06-2017	23-05-2017	-16
6.11	14-06-2017	31-07-2017	23-05-2017	-47

ReleaseNumber	StartDate	EndDate-LoadDate

Again, you need to use aggregation functions in your expression (per default, Only() will be applied around each field if you omit the aggregation functions), and it will depend on your requirement, which function(s) you need to use.