Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

create a flag field

Hi,

How can I show the month dimension, so for each month it will show the last 6 months relative to current month dimension?

For each one of the relations, how can I set a flag field = 1 when the Transaction Month falls in the required period compared to the Display Month.

For example:

Trans. Month     Display Month     Last6Months

Jan                          Jul                         0

Feb                         Jul                         1

Mar                         Jul                         1

Apr                         Jul                         1

May                        Jul                         1

Jun                         Jul                         1

Jul                          Jul                         1

please, help in creating the flag field and then based on my flag field I have to perform some calculations

and how can I use a technique "as of date" and build a link table in the script.

Please,help with some sample script!!

Thanks,

Rohit

Tags (1)
7 Replies
adamdavi3s
Honored Contributor

Re: create a flag field

The problem here is that you can't simply use a flag based on the selected month dimension, as every month will have a last 6 month flag!

If you search on here for "point in time" then you will find some good ideas such as:

Calendar with AsOf Flags, Compare Easter to Easter

For simple set analysis you can use something like:

sum({<Date={'>$(=AddMonths(Max(Date),-6))'}>}>}yourfield)

Not applicable

Re: create a flag field

Hi Adam,

We have tried with below expression as well but it is not giving expected result.

sum({<Date={'>$(=AddMonths(Max(Date),-6))'}>}>}yourfield)

Could you please share some sample qvw to have some understanding.

Thanks,

Rohit

adamdavi3s
Honored Contributor

Re: create a flag field

Hi Rohit,

If you can share a small sample of your data, or some dummy data then I'll knock up a formula for you

Kind regards


Adam

Not applicable

Re: create a flag field

Hi Adam,

Sorry for the late reply.

Please find attached file which is having sample data.

   

Customer IDTransaction DateSales QtyCountry Code
1015-Jan-161US
1115-Jan-162US
1215-Jan-163UK
1315-Feb-162IR
1515-Feb-163US
1615-Feb-165US
1715-Mar-162UK
1815-Mar-166IR
1915-Mar-167US
2015-Mar-168US
2115-Apr-161UK
2215-Apr-164IR
2315-Apr-165US
2415-Apr-166US
2515-Apr-163UK
2615-May-161IR
2715-May-162US
2815-May-163US
2915-Jun-162UK
3015-Jun-163IR
3115-Jun-165US
3215-Jul-162US
3315-Jul-166UK
3415-Jul-167IR
3515-Jul-168US
3615-Aug-161US
3715-Aug-164UK
3815-Aug-165IR
3915-Aug-166US
4015-Aug-163US
4115-Sep-164UK
4215-Sep-165IR
4315-Sep-166US
4415-Oct-163US
4515-Oct-161UK
4615-Oct-162IR
4715-Oct-163US
4815-Nov-162US
4915-Nov-163UK
5015-Nov-165IR
5115-Nov-162US
5215-Nov-166US
5315-Nov-167UK
5415-Nov-168IR
5515-Dec-161US
5615-Dec-164US
5715-Dec-165UK
5815-Dec-166IR
5915-Dec-163US

I want to know more about creating the flag field.

Also , if the current selection month is Apr so it is showing data for rolling last 4 month accumulative value i.e Jan,Feb,Mar and Apr, and a single bar need to be displayed for all four month in bar chart.


Similarly if we want to display rolling 1 year data in bar chart than it should display three bars, in which each bar represent accumulated value of four months.

Khan_Mohammed
Honored Contributor II

Re: create a flag field

I'm not sure if this is what you looking for...

Find the attachment.

I've to alter your data as your data couldn't show last 3 months from today as it will end in Dec 2016 and cannot go rolling 4 before Dec-2016...I guess you understand it.

I used below expression

sum({<[Rolling 4]={1},[Sales Qty]>}[Customer ID])

Not applicable

Re: create a flag field

Hi Aehman,

Could you please provide more information on how the sum data has been calculated with below expression:

sum({<[Sales Qty],[Rolling 4]>}[Customer ID])

Also, could you please let me know any idea on creating the flag field. If you could share some sample QVW that would be helpful to analyze the issue.

Khan_Mohammed
Honored Contributor II

Re: create a flag field

The expression is sum({<[Sales Qty],[Rolling 4]={1}>}[Customer ID])

Not sum({<[Sales Qty],[Rolling 4]>}[Customer ID])


The exp will calculate sales for only previous 4 months where the flag is 1 from Customer ID.

and to create flags, check the script for the QVW I provided you in previous mail...

Check for MC Tab, (Master Calendar)

Capture.PNG