Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi i have to create a dynamic bucket which should take min and max of my date field ,
and accordingly it create a bucket a 30 60 90 and 90+ days bucket.......pls help me out
and my min and max date keeps on changing , main problem is that i have to calculate 30 60 90 90+ from that min date , which is not constant.
you can use interval match to create the buckets
The extended IntervalMatch prefix is used to create a table matching discrete numeric values to one or more numeric intervals, while at the same time matching the values of one or several additional keys.
This is a very powerful and flexible feature that can be used for linking transactions with dimensions that are changing over time: Slowly changing dimensions.
The IntervalMatch prefix must be placed before a Load or Select (SQL) statement that loads the intervals. The table containing the discrete data points and the additional keys must already have been loaded into QlikView before the statement with the IntervalMatch prefix. The prefix transforms the loaded table of intervals and keys to a table that contains an additional column: the discrete numeric data points. It also expands the number of records so that the new table has one record per possible combination of discrete data point, interval and value of the key field(s).
The syntax is:
intervalmatch (matchfield,keyfield1 [ , keyfield2, ... keyfield5 ] ) (loadstatement | selectstatement )
matchfield is the field containing the discrete numeric values to be linked to intervals.
keyfield(s) are fields that contain the additional attributes that are to be matched in the transformation.
loadstatement or selectstatement must result in a table where the first two fields contain the lower and upper limits of each interval and the third and any subsequent fields contain the keyfield(s) present in the IntervalMatch statement. The intervals are always closed, i.e. the end points are included in the interval. Non-numeric limits render the interval to be disregarded (undefined).
In order to avoid undefined interval limits being disregarded, it may be necessary to allow NULL values to map to other fields that constitute the lower or upper limits to the interval. This can be handled by the NullAsValue statement or by an explicit test that replaces NULLs with a numeric value well before or after any of the discrete numeric data points.
Example:
Inner Join IntervalMatch (Date,Key) LOAD FirstDate, LastDate, Key resident Key;
Ankit your question is not complete... Can you please provide some more information or the sample of solution which you are expecting?
hi do you have any sample document for this.
hi manish the requirement is that i have to create a bucket from that sample data , inwhich u will get the date field easily and from that field i have to create a dynamic bucket beacause the data which will change ..and min and max date will also change.
so the problem is that how do i calculate no. of days from today for 30 60 90 90+ days bucket.
datefield - today() if no. of days is <=30 then 30bucket.
if no. of days is >30 and <60 then 60bucket
likewise
Create a Straight Table
Add Calculated Dimension as below
=Aggr(IF((Date(Max(ALL Date)) - Date) <= 30, Dual('<30',1),
IF((Date(Max(ALL Date)) - Date) > 30 and (Date(Max(ALL Date)) - Date) <= 60, Dual('30-60',2),
IF((Date(Max(ALL Date)) - Date) > 60 and (Date(Max(ALL Date)) - Date) <= 90, Dual('60-90',3),
Dual('>90',4)))),Date)
Expression
COUNT(Distinct Date)
or
COUNT(Date)
or
SUM(Amount)
Whatever you want...
Go to Sort Tab and Sort By Numeric...