Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a scenario where I have to check for the data based on the Date field from now.
I have Product Number 1 has Date as July, So I have to check from Today's date to July's data(VOL1 to VOL3).
And if data isn't entered, then I have to (highlight /showing data as Not Entered in Status Column) the null from VOL1 to VOL3 for the null values likewise for every Product Number.
So Total count for
Product 1 is 0(VOL1 to VOL3).i.e From May 2022 to July 2022,
Product 2 is 8(VOL1 to VOL4).i.e From May 2022 to August 2022
I was able to achieve using IF condition, Rather I would prefer to FOR loop due to script performance.
Note: VOL1 to VOL6 will be dynamic as for now, VOL1 is May and in next month VOL1 will be June.
For Reference | Will be dynamic(From the Current Month) -> | May-22 | Jun-22 | Jul-22 | Aug-22 | Sep-22 | Oct-22 |
Product Number | Date | VOL1 | VOL2 | VOL3 | VOL4 | VOL5 | VOL6 |
1 | 7/1/2022 | - | - | - | 1 | 3 | 1 |
2 | 8/1/2022 | 3 | 5 | - | - | 2 | 5 |
Output be like :
can you pls clarify why VOL3 for product 2 is not highlighted in your output. is there a different business rule like an exemption?
I missed out on highlighting it, Thanks for notifying it.
Just use crosstable load
if there is no data for given product for a given month, you will automatically see null
No, Cross Table won't be the solution for my scenario. Thanks for the suggestion
Vinieme12 has a good point. since you dont have data for those points, QS will present it as NULL and will highlight it as gray. furthermore, if all products dont have volume for a specific Month, that month may not even show up.
what i would do is pad those missing data points with a record but for your volume field fill these padded records with a value 'NULL' for example. now those data points can be charted and unfortunately you will need to use IF statements (youre going to need a few levels of ifs - if the data point falls under the date period, if the data point is a padded data point) in the expression to determine if the value were first of all a padded value and from there you will be able to add in format rules like background.
you can add in a row type field to make it easier on the IF statement. if its a padded data point your field (row_type for example = 'NULL' and if not padded row_type='NOT NULL' however you want) then you can test this field to decide if you will highlight it or not.
hope that guides you to a final solution
Is there any possible to sum up in a field based on the logic mentioned above?