Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Creating Dynamic Master Items

Hi Guys,

I have a question:

I have uploaded in my app a sales record with some attributes as day, product sold, quantity, price….(all columns).

Day       Product             quantity   price
1 Nov   Red shoes           2             7$
1 Nov   Blue shoes          1             2$

...

2 Nov

3 Nov

....


I have created a master item called months where I add all the days of the month,

And a master item called week, where I add the weeks.

Now, what I want to do is to create three different measures

1) Last 30 days

2) Last 7 days

3) Last 3 days

Basically what I would like to do is to put these three measures in a table as column, in a way that I can see something like

PRODUCT                              Last 30 days          last 7 days          last 3 days


Black shoes sold                              25                         6                         2

Red shoes sold                                95                         12                        7

............


And so on.

The goal would be to create “Auto updating master measures”

This means that every day, when I replace the existing doc with an updated version, my app should be able to scan all the days in the “days” column, find the last 30, last 7 and last 3, and update the master measures.

The product column is then calculated including the master measure in the calculation and it’s automatically updated.

Is this possible?

Thank you in advance

9 Replies
cjburrow
New Contributor III

Re: Creating Dynamic Master Items

See these posts about rolling period calculations:

Set Analysis for Rolling Periods

Re: Set Analysis Rolling Period

mark6505
Valued Contributor III

Re: Creating Dynamic Master Items

Hi,

Something like the below in set analysis

SUM({<Day={'<=Today(),>=Today()-3}>}quantity)

You have to be careful of the date formats though.

Mark

Not applicable

Re: Creating Dynamic Master Items

Hi Mark,
Thanks for your reply,

the thing is that I export a table with the first column like

Day

1/1/15

1/2/15

1/3/15

1/4/15

1/5/15

so I have first to say to the app what is "today", which should be the "highest" value of the column

From it, creating a dynamic item called "Last 7 days" that can basically fill a table like

Day
....
...
...
...

...

...

today


Same for last 3 days

Day

....

...

...

Today

You see what I mean?

Best Regards,

P

mark6505
Valued Contributor III

Re: Creating Dynamic Master Items

Hi

You could try MAX(Date) instead of the today().

Another option it is to do something in script.

ThreeDay:

Load

  Product,           

   SUM(quantity)      as 3dayQTY,

  SUM(price)          as 3dayPrice

Resident 'Your table'

where Date > MAX(Date) - 3

GROUP BY Product;


Mark

Not applicable

Re: Creating Dynamic Master Items

Thanks I will have a look now!

Not applicable

Re: Creating Dynamic Master Items

Hi Mark,

This is a good idea but I guess it would be quite difficult since I have to put together data coming from different files and match them in the app

But I will give it a try if I don't find other solutions!

Cheers

P

Not applicable

Re: Creating Dynamic Master Items

You can create expression for each measure in straight table like below:

Assume you have date field in your Master Items table

Last 30 Days : Sum({DateField={">=$(=Max(DateField)-30)<=$(=Max(DateField))"}}quantity)

Last 7 Days : Sum({DateField={">=$(=Max(DateField)-7)<=$(=Max(DateField))"}}quantity)

Last 3 Days : Sum({DateField={">=$(=Max(DateField)-3)<=$(=Max(DateField))"}}quantity)

Not applicable

Re: Creating Dynamic Master Items

Hi,

Thanks so much, I feel that I am close to what I want but it still doesn't work.

I'd like to point this out

All the products, the spend and all the other attributes are already aggregated by day.
It means that I am already able to plot a table where all the data are aggregated (with sum)

I can already have smth like

Date             Product            Quantity        Price per unit    Total Price         Shop
1 Nov            Blue shoes        4                 30                        120               London

1 Nov            Red shoes         5                 30                        150              Chelsea

2 Nov            Blue shoes        2                 30                        60              London

............

...........

.........

.........

what I want to do is only to manipulate the date column

so I want to be able to plot 3 tables at the same time

last 3

last 7

last 30

so I can have in real time 3 separate tables with a manipulate date column, and the rest (should) come automatically.

Hope it's clear

(I am quite new with Qlik so sorry if it may look a very basic question)

Best

P

Not applicable

Re: Creating Dynamic Master Items

Hi,

I would like to show only data for last 7 days of last week, as per my requirement my weekstarts from Friday and ends on Thursday, so currently for ex: today is Monday so I want to show the data from Friday 22nd January to Thursday January 28th with the measure.

My expression will be:

=Sum({Calculation} Sales)

Please find the attached screenshot for the same.

Please help on this, thanks

Community Browser