Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

7 Replies
adamdavi3s
Master
Master

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
Author

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
Master
Master

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
Author

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.

MK9885
Master II
Master II

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
Author

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.

MK9885
Master II
Master II

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